
package server;

import java.io.FileWriter;
import java.io.IOException;
import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.text.SimpleDateFormat;

import javax.swing.JFrame;
import javax.swing.JOptionPane;

import org.apache.poi.ss.formula.atp.DateParser;
import org.jfree.data.time.TimeSeries;

import utilities.Services;
import entities.DashboardGauges;
import entities.Employee;
import entities.EngraveComment;
import entities.EngraveReportEntity;
import entities.FailReport;
import entities.FailSet;
import entities.PolishFrameEntity;
import entities.PolishReportEntity;
import entities.ProductionOrder;
import entities.QCReport;
import entities.QCReportEntity;
import entities.RepolishSet;
import entities.RequestParameters;
import entities.StartPolishEntity;
import entities.VPOfOperationsTabbedDiscardReportEntity;
import entities.VPOfOperationsTabbedHashmashaReportEntity;
import entities.VPOfOperationsTabbedPackagingReportEntity;
import entities.WIP;
import gui.VPOfOperations.VPOfOperationsFailPercentagesReport;
/**
 *
 * @author Idan, Adam
 * This class holds all the functions called by the clients and executed by the mySQL database.
 */
public class mySQL
{
    
    
    // connect to DB

    private static final String String = null;
    private Connection conn;

    public mySQL (String sqlServer, String sqlUsername, String sqlPassword)
    {
        try
        {
             Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception ex) {}

        try {


          
            this.conn=DriverManager.getConnection("jdbc:" + sqlServer+"?user="+sqlUsername+"&password="+sqlPassword+"&useUnicode=true&characterEncoding=UTF-8");
          
        } catch (SQLException ex)
        {
        	JOptionPane.showMessageDialog(new JFrame(),
				    "שגיאה בפרטי התחברות לבסיס הנתונים.","שגיאה",JOptionPane.ERROR_MESSAGE);
        	
        	
        	System.exit(0);
			
			
          
        }
    }


        //Methods

    
    
    /** 
     * @return list of orders waiting for engrave
     */
    public ArrayList<Object> getListOfOrders ()
    {
    	//System.out.println("Begin creating list of orders.");
    	
        ArrayList<Object> listOfOrders = new ArrayList<Object>();
        listOfOrders.add("listOfOrders");
        int counter = 0;
        listOfOrders.add(counter);
        
        try
        {
	        Statement stmt= conn.createStatement();
	        ResultSet rs = stmt.executeQuery("SELECT orderID, type, diopter, DATE_FORMAT(orderdate,'%d/%m/%Y'), quantity FROM hanitdb.orders Where statusID = 1 ORDER BY orderdate ASC;  ");

 
	      
	        while(rs.next())
	        {
	        	listOfOrders.add(rs.getString(1)); //Get orderID  
                listOfOrders.add(rs.getString(4));//Get orderDate
                listOfOrders.add(rs.getString(2)); //Get type
                listOfOrders.add(rs.getString(3)); //Get diopter
                listOfOrders.add(rs.getString(5)); //Get quantity 
                counter++;
	        }
	        
	        
	        
	        
	        listOfOrders.set(1,counter);
	        
	        
	        stmt.close();
	        rs.close();
	   
        }
        catch (SQLException e)
        {
	      
	         //e.printStackTrace();
        }
        
    
        return listOfOrders;
    }

    /**
     * Gets list of products
     * @return
     */
    public ArrayList<Object> getListOfProducts()
    {

    	 ArrayList<Object> listOfProducts = new ArrayList<Object>();
    	 listOfProducts.add("listOfProducts");
 
         int counter = 0;
         listOfProducts.add(counter);
         
         try {
         Statement stmt= conn.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT productName FROM hanitdb.products; ");

         while(rs.next())
             {
	        	 listOfProducts.add(rs.getString(1)); //Get productName
	        	 counter++;
             } 
         listOfProducts.set(1,counter);
         
         
         stmt.close();
         rs.close();
         } catch (SQLException e)
         {
        
          //e.printStackTrace();
         }
  
         return listOfProducts;
	}

    /**
     * 
     * @return data for MPM station
     */
	public ArrayList<Object> initializeAllFieldsMPM()
	{
	
		ArrayList<Object> listOfOrders = getListOfOrders();
		ArrayList<Object> listOfProducts = getListOfProducts();
		ArrayList<Object> response = new ArrayList<Object>();
		response.add("initializeAllFieldsMPM");
		response.add(listOfOrders);
		response.add(listOfProducts);
	//	//System.out.println("Finished the array, returning to server.");
		return response;
	}

/**
 * 
 * @param newOrder order data
 * @return validity string
 */
	public ArrayList<Object> createNewOrder(ProductionOrder newOrder)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		Statement stmt;
		int result;
		
		try
		{
			stmt = conn.createStatement();
			
			result = stmt.executeUpdate(" INSERT IGNORE INTO orders(orderID,type,diopter,orderDate,quantity,statusID) VALUES('" + newOrder.getOrderID() + "','" + newOrder.getType() +"','" + newOrder.getDiopter() + "','" + newOrder.getOrderDate() + "','" + newOrder.getQuantity() +  "','" + newOrder.getStatusID() + "');");
			
			
			
			if (result==1)
			{
				//valid
				response.add("Valid");
				
			}
			else
			{
				//failed
				response.add("Failed in createNewOrder");
			}
			
			
		} 
		catch (SQLException e)
		{
		
			//e.printStackTrace();
			response.add("SQL Failed");
		}
		
		
			
		
		return response;
		  
	}

/**
 * 
 * @param productionOrder order data to delete
 * @return validity string
 */
	public ArrayList<Object> deleteOrder(ProductionOrder productionOrder) 
	{
		
		ArrayList<Object> response = new ArrayList<Object>();
		Statement stmt;
		int result;
		try
		{
			stmt = conn.createStatement();
			result = stmt.executeUpdate("DELETE FROM orders WHERE `orderID`='" + productionOrder.getOrderID() + "';");

			if (result==1)
			{
				//valid
				response.add("Valid");
			}
			else
			{
				//failed
				response.add("failedToDeleteOrder");
			}
			
			
		} 
		catch (SQLException e)
		{
		
			//e.printStackTrace();
			response.add("SQL Failed");
		}
		

		
		return response;
	}

/**
 * 
 * @return data for Engraving initialization
 */
	public ArrayList<Object> initializeAllFieldsEngraving()
	{
		ArrayList<Object> response = new ArrayList<Object>();
		ArrayList<Object> listOfEmployees = new ArrayList<Object>();
		ArrayList<Object> listOfComments = new ArrayList<Object>();
		ArrayList<Object> listOfReports = new ArrayList<Object>();
		ArrayList<Object> listOfOrders = new ArrayList<Object>();
		ArrayList<Object> historyOfReports = new ArrayList<Object>();

	     response.add("initializeAllFieldsEngraving"); // index 0
	     
	     listOfEmployees = getListOfEmployees();
	     listOfComments = getListOfComments();
	     listOfReports = getListOfReports();
	     listOfOrders = getListOfOrders2();
	     historyOfReports = getHistoryOfReports();
      
	     response.add(listOfEmployees); // index 1
	     response.add(listOfComments); // index 2
	     response.add(listOfReports); // index 3
	     response.add(listOfOrders); // index 4
	     response.add(historyOfReports); // index 5
	    
	     return response;
	
	}


/**
 * 
 * @return list of employees
 */
	public ArrayList<Object> getListOfEmployees()
	{
		ArrayList<Object> listOfEmployees = new ArrayList<Object>();
		int employeeCounter = 0;
		listOfEmployees.add(employeeCounter);
		try
	        {
		        Statement stmt= conn.createStatement();
		      
		        ResultSet rs = stmt.executeQuery("SELECT employeeID,name FROM hanitdb.employees WHERE employeeID > 0 ORDER BY name DESC;");

		      
				while(rs.next())
		        {	
		        	Employee employee = new Employee(rs.getString(2),rs.getString(1) );
		        	listOfEmployees.add(employee);	
		        	employeeCounter++;
		        }
		        
		        listOfEmployees.set(0,employeeCounter);
		        
		     
		        
		        stmt.close();
		        
		        rs.close();
		        
	        }


		catch (SQLException e)
		{
	       
	         //e.printStackTrace();
		}
		
		return listOfEmployees;
	}
	
	/**
	 * 
	 * @return list of comments used in engraving
	 */
	public ArrayList<Object> getListOfComments()
	{
		
		ArrayList<Object> listOfComments = new ArrayList<Object>();
		int commentCounter = 0;
		listOfComments.add(commentCounter);
		
		  try
	        {
		      
		        Statement stmt2= conn.createStatement();
		       
		      
		        ResultSet rs2 = stmt2.executeQuery("SELECT engraveCommentID,comment FROM hanitdb.engravecomments where engraveCommentID > 0;");
		       
		    
		        while (rs2.next())
		        {
		        	EngraveComment comment = new EngraveComment();
		        	comment.setCommentID(Integer.parseInt(rs2.getString(1)));
		        	comment.setComment(rs2.getString(2));
		        	listOfComments.add(comment);
		        	commentCounter++;
		        }
		        
		        listOfComments.set(0, commentCounter);
		       
		       
		        stmt2.close();
		        
		   
		        rs2.close();
		       
	        }


      catch (SQLException e)
      {
	      
	         //e.printStackTrace();
      }
		  
		  return listOfComments;
		  
	}
	
	
	/**
	 * 
	 * @return list of engrave reports in the system waiting for seperation
	 */
	public ArrayList<Object> getListOfReports()
	{
		ArrayList<Object> listOfReports = new ArrayList<Object>();
		int reportsCounter = 0;
		listOfReports.add(reportsCounter);
		
		 try
	        {
		      
		       
		        Statement stmt3= conn.createStatement();
	      
		        ResultSet rs3 = stmt3.executeQuery("SELECT engrave.orderID,orders.type,orders.diopter,orders.quantity,engrave.engraveDate,employees.name,engrave.sideAquantity,engrave.sideBquantity,engrave.engraveID FROM hanitdb.engrave, hanitdb.employees,hanitdb.orders where orders.statusID=2 AND engrave.orderID=orders.orderID AND employees.employeeID=engrave.employeeID;");
	    
		        while (rs3.next())
		        {
		        	ProductionOrder report = new ProductionOrder();
		        	report.setOrderID(rs3.getInt(1));
		        	report.setType(rs3.getString(2));
		        	report.setDiopter(rs3.getFloat(3));
		        	report.setQuantity(rs3.getInt(4));

		        	//String formatted = new SimpleDateFormat("dd/MM/yyyy").format(rs3.getTimestamp(5));
		        	report.setEngraveDate(rs3.getTimestamp(5));
		        //	report.setEngraveDateAsString("01/01/2014");
		        	report.setEmployee(rs3.getString(6));
		        	report.setReportSideA(rs3.getInt(7));
		        	report.setReportSideB(rs3.getInt(8));
		        	report.setEngraveID(rs3.getInt(9));
		        	
		        	listOfReports.add(report);
		        	
		        	reportsCounter++;
		        	
		        }
		        
		        listOfReports.set(0, reportsCounter);
		       
		      
		        stmt3.close();

		   
		     
		        rs3.close();

	        }


     catch (SQLException e)
     {
	         //e.printStackTrace();
     }
		 
		 
		 return listOfReports;
	}
	
	/**
	 * 
	 * @return reports done in the last 60 days
	 */
	private ArrayList<Object> getHistoryOfReports()
	{
		ArrayList<Object> listOfReports = new ArrayList<Object>();
		int reportsCounter = 0;
		listOfReports.add(reportsCounter);
		
		 try
	        {
		      
		       String today = Services.getTodayDifferentFormat2();
		       String twoMonthsAgo = Services.getTwoMonthsAgo();
		        Statement stmt3= conn.createStatement();
		        
		      
		        ResultSet rs3 = stmt3.executeQuery("SELECT engrave.orderID,orders.type,orders.diopter,orders.quantity,engrave.engraveDate,employees.name,engrave.sideAquantity,engrave.sideBquantity,engrave.engraveID FROM hanitdb.engrave, hanitdb.employees,hanitdb.orders where engrave.orderID=orders.orderID AND employees.employeeID=engrave.employeeID AND engrave.engraveDate <= '" + today + "' AND engrave.engraveDate >= '" + twoMonthsAgo + "' ORDER BY engrave.engraveDate ASC;");
	    
		        while (rs3.next())
		        {
		        	ProductionOrder report = new ProductionOrder();
		        	report.setOrderID(rs3.getInt(1));
		        	report.setType(rs3.getString(2));
		        	report.setDiopter(rs3.getFloat(3));
		        	report.setQuantity(rs3.getInt(4));

		        	//String formatted = new SimpleDateFormat("dd/MM/yyyy").format(rs3.getTimestamp(5));
		        	report.setEngraveDate(rs3.getTimestamp(5));
		        //	report.setEngraveDateAsString("01/01/2014");
		        	report.setEmployee(rs3.getString(6));
		        	report.setReportSideA(rs3.getInt(7));
		        	report.setReportSideB(rs3.getInt(8));
		        	report.setEngraveID(rs3.getInt(9));
		        	
		        	listOfReports.add(report);
		        	
		        	reportsCounter++;
		        	
		        }
		        
		        listOfReports.set(0, reportsCounter);
		       
		      
		        stmt3.close();

		   
		     
		        rs3.close();

	        }


     catch (SQLException e)
     {
	      
	         //e.printStackTrace();
     }
		 
		 
		 return listOfReports;
	}
	
	public ArrayList<Object> getListOfOrders2()
	{

		ArrayList<Object> listOfOrders = new ArrayList<Object>();
		int ordersCounter = 0;
		listOfOrders.add(ordersCounter);
		
        try
        {
	      
	      
	        Statement stmt4= conn.createStatement();
	    
	        ResultSet rs4 = stmt4.executeQuery("SELECT orderID,type,diopter,DATE_FORMAT(orderdate,'%d/%m/%Y'),quantity FROM orders WHERE statusID < 3 ORDER BY orderID ASC;");

	        
	        while (rs4.next())
	        {
	        	ProductionOrder order = new ProductionOrder();
	        	order.setOrderID(rs4.getInt(1));
	        	order.setType(rs4.getString(2));
	        	order.setDiopter(rs4.getFloat(3));
	        	order.setDateAsString(rs4.getString(4));
	        	order.setQuantity(rs4.getInt(5));

	        	listOfOrders.add(order);
	        	ordersCounter++;
	        
	        }
	        
	        listOfOrders.set(0, ordersCounter);
	        

		        stmt4.close();

	        rs4.close();  
        }


    catch (SQLException e)
    {
    
         //e.printStackTrace();
    }
        
        return listOfOrders;
	}
	
	
	
	/**
	 * 
	 * @param orderID order parameters
	 * @return production order data in the database \ validity string
	 */
	public ArrayList<Object> getOrderIdData(String orderID) 
	{

        ArrayList<Object> orderData = new ArrayList<Object>();
        orderData.add("getOrderIdData");
    
        try
        {
	        Statement stmt= conn.createStatement();
	        ResultSet rs = stmt.executeQuery("SELECT orderID, type, diopter, DATE_FORMAT(orderdate,'%d/%m/%Y'), quantity,statusID FROM hanitdb.orders Where orderID='" + orderID + "';");
	
	        
	        // get all reports for the orderID
	        // sum side A and side B
	       
	      
	        
	      
	      if(rs.next())
	      {
	    	  // Found order in the DB
	    	  ProductionOrder order = new ProductionOrder();
	    	  order.setDateAsString(rs.getString(4));
	    	  order.setDiopter(Float.valueOf(rs.getString(3)));
	    	  order.setOrderID(Integer.parseInt(orderID));
	    	  order.setQuantity(Integer.parseInt(rs.getString(5)));
	    	  order.setStatusID(Integer.parseInt(rs.getString(6)));
	    	  order.setType(rs.getString(2));
	    	  orderData.add("valid");
	    	  
	    	  try
	    	  {
	    		  Statement stmt2= conn.createStatement();
	    		  ResultSet rs2 = stmt2.executeQuery("SELECT orderID,SUM(sideAquantity),SUM(sideBquantity) FROM hanitdb.engrave Where orderID='" + orderID + "';");
	    		  if (rs2.next())
		    	  {
	    			  order.setSideA(rs2.getInt(2));
	    			  order.setSideB(rs2.getInt(3));
		    	  }
	    		  
	    		  
	    	  }
	    	  catch (SQLException e)
	          {
	          	orderData.set(1,"SQL Failed (No report for the orderID exist");
	  	       
	  	         //e.printStackTrace();
	          }
		      
	    	  
	    	  orderData.add(order);
	      }
	      else
	      {
	    	  // Didn't find order in the DB
	    	  orderData.add("invalid");
	    	  
	      }
	        stmt.close();
	        rs.close();
        }
        catch (SQLException e)
        {
        	orderData.add("SQL Failed");
	       
	         //e.printStackTrace();
        }
        
   
        return orderData;
	}

/**
 * 
 * @param productionOrder engrave report data
 * @return validity string
 */
	public ArrayList<Object> newEngraveReport(ProductionOrder productionOrder)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		
		Statement stmt;
		Statement stmt2;
		Statement stmt3;
		int result=0;
		int lastInsertID=0;

		try
		{
			stmt = conn.createStatement();
			stmt2 = conn.createStatement();
			stmt3 = conn.createStatement();
			result = stmt.executeUpdate("INSERT INTO engrave(orderID,engraveDate,line,employeeID,sideAquantity,sideBquantity,comments1,comments2,freeText,shift) VALUES('" + productionOrder.getOrderID() + "','" + productionOrder.getEngraveDate() +"','" + productionOrder.getLine() + "','" + productionOrder.getEmployeeID() + "','" + productionOrder.getReportSideA() +  "','" + productionOrder.getReportSideB() + "','" + productionOrder.getCommentid1() + "','" + productionOrder.getCommentid2() + "','" + productionOrder.getEngraveRemarks() +  "','" + productionOrder.getShift() + "');");
			stmt3.executeUpdate("INSERT IGNORE INTO materials(materialID,orderID) VALUES('" + productionOrder.getRawMaterial() + "','" + productionOrder.getOrderID() + "');");
			if (result==1)
			{
				//valid
				response.add("Valid");
				// get the engraveID and return it
				ResultSet rs = stmt2.executeQuery("SELECT LAST_INSERT_ID();");
				rs.next();
				lastInsertID=rs.getInt(1);
		
				response.add(lastInsertID);
		
			}
			else
			{
				//failed
				response.add("Failed in newEngraveReport");
			}
			
			stmt.close();
						stmt3.close();
			
		} 
		catch (SQLException e)
		{
		
			//e.printStackTrace();
			response.add("SQL Failed");
		}

		// Set statusID
		try
		{
			stmt2 = conn.createStatement();
			stmt2.executeUpdate("UPDATE orders SET `statusID`='" + productionOrder.getStatusID() +"' WHERE `orderID`='" + productionOrder.getOrderID() + "';");
			stmt2.close();

		} 
		catch (SQLException e)
		{
		
			//e.printStackTrace();
			response.add("SQL Failed");
		}

		
		return response;
	}
/**
 * Initializes the main polish panel , and returns all the relevant fields : jarID ,beginDate ,originalEndDate ,endDate ,topline ,pelletesCycles , pelletesCycles 
 * @return ArrayList orderData	which always contains at index 0 : valid \ unvalid , index 1,2... : polishEntit
 * @throws SQLException
 */
	public ArrayList<Object> initializePolishPane() throws SQLException {
		// TODO Auto-generated method stub
 		ResultSet rs = null;
		Statement stmt = null;
		 int jarNum ,pelletescycles,topline,countToEnd;
		 Date originalEndDate,EndDate,beginDate;
		 long totalDays , extraDays;
		ArrayList<Object> orderData = new ArrayList<Object>();
		 orderData.add("initializePolishPane");
	  //   //System.out.println("Run initializePolishPane() here");
	     try
	        {
		        stmt= conn.createStatement();
		        rs = stmt.executeQuery("select jars.jarID ,   polish.beginDate,   polish.originalEndDate,    polish.endDate,    polish.topline,   jars.pelletesCycles from hanitdb.polish , hanitdb.ordersinpolish , hanitdb.jars , orders where polish.jarID=jars.jarID AND polish.polishID=ordersinpolish.polishID AND orders.orderID=ordersinpolish.orderID AND orders.statusID='31' group by jars.jarID ");
		      //  //System.out.println("Got back ResultSet of orders.");
	        }
	     catch(SQLException e) {
	    //	//System.out.println("System Err - couldnt retrive data \n code name :initializePolishPane \n "); 
	    	orderData.add(1,"unvalid");
	    	rs.close();
	    	stmt.close();
	    	return orderData;
	     }
	     if(!rs.next())
	     {
	   
	    	 orderData.add("unvalid");
	    	 rs.close();
		     stmt.close();
	    	 return orderData;
	     }
	     else 
	     {
		     do
		        {
		    	 try{
		    	 	PolishFrameEntity polishEntit = new PolishFrameEntity();
		        	jarNum=rs.getInt(1);
		        	pelletescycles=rs.getInt(6);
		        	topline=rs.getInt(5);
		        	originalEndDate=rs.getDate(3); //יציאה מקורי
		        	EndDate=rs.getDate(4);		//יציאה סופי
		        	beginDate=rs.getDate(2);		// 
		        	totalDays =EndDate.getTime() - beginDate.getTime();  //סה"כ ימי הליטוש כולל תוספת זמן
		        	totalDays=totalDays / (1000 * 60 * 60 * 24);
		        	extraDays=EndDate.getTime() - originalEndDate.getTime();
		        	extraDays=extraDays / (1000 * 60 * 60 * 24);
		   
		        	polishEntit.setJarNum(jarNum); polishEntit.setPelletescycles(pelletescycles); polishEntit.setTopline(topline); polishEntit.setOriginalEndDate(originalEndDate); polishEntit.setEndDate(EndDate); polishEntit.setTotalDays(totalDays); polishEntit.setExtraDays(extraDays);polishEntit.setBeginDate(beginDate);
		        	orderData.add(polishEntit);
		        	
		    	 }catch(Exception e)
		    	 {
		    		 //e.printStackTrace();
		    
		    		 System.exit(0);
		    	 }
		        }while(rs.next());
		    }
	        orderData.add(1,"valid");
	     
	        stmt.close();
	        rs.close();
	 
		return orderData;
	}

	/**
	 * Initializes the startpolish panel  .
	 * @return	ArrayList respond	which always contains at index 0:"initializeStartPolish" , 1 : valid \ unvalid , index 2,3... : StartPolishEntity 
	 * @throws SQLException
	 */
	public ArrayList<Object> initializeStartPolish(ArrayList<Object> message) throws SQLException {
	
		ResultSet rs = null;
		Statement stmt=null;
		String jarID, pelletesCycles ,orderID,topline,comments;
		 long totalDays;
		 Date endDate,beginDate;
		 ArrayList<Object> myMessage;
		 myMessage=message;
		ArrayList<Object> orderData = new ArrayList<Object>();
		orderData.add("initializeStartPolish");
		jarID=myMessage.get(1).toString();
		////System.out.println("On mySql server : Run initializeStartPolish()");
		try{
			stmt= conn.createStatement();
			rs=stmt.executeQuery("select jars.pelletesCycles , polish.topline,polish.endDate ,ordersinpolish.orderID, polish.beginDate ,polish.comment from orders, jars ,polish , ordersinpolish where orders.statusID = '31' AND jars.jarID='"+jarID+"' AND polish.polishID=ordersinpolish.polishID AND jars.jarID=polish.jarID AND ordersinpolish.orderID=orders.orderID;");
			
		}
		catch(SQLException e){
			orderData.add(1,"unvalid");
	
			//e.printStackTrace();
			return orderData;
		}
		if(!rs.next())
	     {
	  
	    	 orderData.add(1,"unvalid");
	    	 return orderData;
	     }
		else{
			do
			{
				pelletesCycles=rs.getString(1);
				topline=rs.getString(2);
				endDate=rs.getDate(3);
				orderID=rs.getString(4);
				beginDate=rs.getDate(5);
				totalDays =endDate.getTime() - beginDate.getTime();
	        	totalDays=totalDays / (1000 * 60 * 60 * 24);
	        	comments=rs.getString(6);
				StartPolishEntity myEntit = new StartPolishEntity(orderID,pelletesCycles,topline,endDate,totalDays);
				orderData.add(myEntit);
			}while(rs.next());
			orderData.add(1,"valid");
			
		}
		rs.close();
		stmt.close();
		
	
		return orderData;
	}

	/**
	 * Returns the jarID of all relevant jars .
	 * @return	ArrayList respond	which always contains at index 0 : valid \ unvalid , index 1,2... : jarID (String)
	 * @throws SQLException
	 */
	public ArrayList<Object> initializeComboJarIDBox() throws SQLException {
		Statement stmt=null;
		ResultSet rs=null;
		ArrayList<Object> respond = new ArrayList<>();
		respond.add("initializeComboJarIDBox");
		stmt= conn.createStatement();
		rs=stmt.executeQuery("Select hanitdb.jars.jarID from hanitdb.jars");
	
		if(!rs.next())
		{
			respond.add(1,"unvalid");
			return respond;
		}
		while(rs.next())
		{

			respond.add(rs.getInt(1));
		}
		respond.add(1,"valid");
		return respond;
	}

	/**
	 * Returns the orderID comboBox of all relevant orderID's (status 3 or 5).
	 * @return	ArrayList respond	which always contains at index 0 : valid \ unvalid , index 1,2... : orderID (String)
	 * @throws SQLException
	 */
	public ArrayList<Object> initializeOrderIDComboBox() throws SQLException {
		Statement stmt=null;
		ResultSet rs=null;
		ArrayList<Object> respond = new ArrayList<>();
		respond.add("initializeOrderIDComboBox");
		stmt=conn.createStatement();
		rs=stmt.executeQuery("SELECT hanitdb.orders.orderID from hanitdb.orders where hanitdb.orders.statusID='3' OR orders.statusID='5' ;");
		if(!rs.next())
		{
			respond.add(1, "unvalid");
			return respond;
		}
		else{
			do
			{
	
				respond.add(rs.getInt(1));
			}
			while(rs.next());
		}
		respond.add(1, "valid");
		
		return respond;
	}
	/**
	 * This method adds new polish record to 2 tables "polish" , "orders in polish"  , and sets the order's status to 31 .
	 * @param message	contains 0:addNewPolish , 1:jarID ,2:orderID ,3:daysOfPolish ,4:topline ,5:comments 
	 * @return	ArrayList respond	0:addNewPolish , 1: valid \ unvalid 
	 * @throws SQLException
	 */
	public ArrayList<Object> addNewPolish(ArrayList<Object> message) throws SQLException {
		int result,originalEndDate;
		Statement stmt,stmt2,stmt3 = null;
		ResultSet rs = null,rs2 =null;
		ArrayList<Object> respond = new ArrayList<>();
		respond.add("addNewPolish");
		String jarID=message.get(1).toString();
		String orderID = message.get(2).toString();
		int intOrderID = Integer.parseInt(orderID);
		String comments = message.get(5).toString();
		DateFormat today = new SimpleDateFormat("yyyy/MM/dd");
		Calendar cal = Calendar.getInstance(); // cal holds the day of current day .
		Calendar cal1 = Calendar.getInstance(); //cal1 holds the end date of polish as the user entered.

		stmt=conn.createStatement();
		stmt2=conn.createStatement();
		String daysOfPolish = message.get(3).toString();
		String topline = (java.lang.String) message.get(4);
		int intDaysOfPolish = Integer.parseInt(daysOfPolish);
		cal1.add(Calendar.DATE,intDaysOfPolish);

		//Receives the order Id's to check if it already exists .
		try{
			rs=stmt.executeQuery("SELECT orr.orderID FROM hanitdb.polish AS po , hanitdb.orders AS orr ,hanitdb.ordersinpolish AS orpo WHERE po.polishID=orpo.polishID AND orr.orderID = orpo.orderID AND orr.StatusID = '31' AND po.jarID = '"+jarID+"'; ");
		}
		catch(SQLException e){
			respond.add(1,"unvalid");
	
			return respond;
		}
		if(rs.next())
		{
			do{
	
				if((intOrderID) == ((int)rs.getInt(1)))
				{
					respond.add(1,"unvalid");
					respond.add(orderID);
				}	
			}while(rs.next());
		}
		try
		{
			//The following statement updates the statusID to be 31 since this orderID is set to be polished.
			result=stmt.executeUpdate("update hanitdb.orders set statusID='31' where orderID = '" + intOrderID + "';");
	
			try{
				//the following statement is inserting a new row to polish table since the orderID is set to be in polish in a new jarID.
				result = stmt.executeUpdate("INSERT INTO hanitdb.polish (jarID ,beginDate ,originalEndDate ,endDate ,topLine ,comment) VALUES ('"+jarID+"','"+today.format(cal.getTime())+"','"+today.format(cal1.getTime())+"','"+today.format(cal1.getTime())+"','"+topline+"','"+comments+"');");
		
			}catch(SQLException e1)
			{
				e1.printStackTrace();
		
				System.exit(0);
			}
			try{
				// This query retrieves the last line was added by the query above , it returns the polish ID needed to be inserted to the ordersinpolish table next line .
				rs2 = stmt2.executeQuery("select * from hanitdb.polish Order by polishID desc limit 1;");
			}catch(SQLException e2)
			{
				e2.printStackTrace();
	
				respond.add(1,"unvalid");
				return respond;
			}
			rs2.next();
			int temp = rs2.getInt(1);
			try{
				result = stmt.executeUpdate("INSERT INTO hanitdb.ordersinpolish (orderID ,polishID ,beginDate ,repolishFlag) VALUES('"+orderID+"','"+rs2.getInt(1)+"','"+today.format(cal.getTime())+"','0');");
	
			}catch(SQLException e3)
			{
				e3.printStackTrace();
	
				System.exit(0);
			}
			respond.add("valid");
			return respond;
		}
		catch(SQLException e)
		{
			respond.add(1,"unvalid");
	
			return respond;
		}

	}

/**
 * 
 * @param separationReport report data
 * @return validity string
 */
	public ArrayList<Object> newSeparationReport(ProductionOrder separationReport)
	{
ArrayList<Object> response = new ArrayList<Object>();
		
		Statement stmt;
		int result;
		Statement stmt2;
	
		try
		{
			stmt = conn.createStatement();
			result = stmt.executeUpdate("INSERT INTO seperation (orderID,employeeID,seperationDate,shift) VALUES('" + separationReport.getOrderID() + "','" + separationReport.getEmployeeID() +"','" + separationReport.getEngraveDate() + "','" + separationReport.getShift() + "');");			
			if (result==1)
			{
				//valid
				response.add("Valid");
			}
			else
			{
				//failed
				response.add("Failed in newSeparationReport");
			}
			
			
		} 
		catch (SQLException e)
		{
		
			//e.printStackTrace();
			response.add("SQL Failed");
			
		}

		// Set statusID
		try
		{
			stmt2 = conn.createStatement();
			stmt2.executeUpdate("UPDATE orders SET `statusID`='3' WHERE `orderID`='" + separationReport.getOrderID() + "';");
		} 
		catch (SQLException e)
		{
	
		//	e.printStackTrace();
			response.add("SQL Failed");
		}

		
		return response;
	}

/**
 * 
 * @param productionOrder parameters for the report to remove
 * @return validity string
 */
	public ArrayList<Object> deleteEngraveReport(ProductionOrder productionOrder)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		
		// get orderID's status

		try
		{
			Statement stmt= conn.createStatement();
			Statement stmt2= conn.createStatement();
			Statement stmt3= conn.createStatement();
	        ResultSet rs;
	        ResultSet rs2;
			rs2 = stmt.executeQuery("SELECT statusID FROM hanitdb.orders Where orderID='" + productionOrder.getOrderID() + "';");
		      if(rs2.next())
		      {
		    	  // Found order in the DB
		    	  String status = rs2.getString(1);

				   
		    	  if (status.equals("2"))
		    	  {
		    			ArrayList<Object> listOfReports = new ArrayList<Object>();
		    			int reportsCounter=0;
		    			listOfReports.add(reportsCounter);
		    		  // delete report
		    		  stmt2.executeUpdate("DELETE FROM engrave WHERE `engraveID`='" + productionOrder.getEngraveID() + "';");
		    		  // get new list of reports
		    		  
		    		  rs = stmt3.executeQuery("SELECT engrave.orderID,orders.type,orders.diopter,orders.quantity,engrave.engraveDate,employees.name,engrave.sideAquantity,engrave.sideBquantity,engrave.engraveID FROM hanitdb.engrave, hanitdb.employees,hanitdb.orders where orders.statusID=2 AND engrave.orderID=orders.orderID AND employees.employeeID=engrave.employeeID;");
				        while (rs.next())
				        {
				        	ProductionOrder report = new ProductionOrder();
				        	report.setOrderID(rs.getInt(1));
				        	report.setType(rs.getString(2));
				        	report.setDiopter(rs.getFloat(3));
				        	report.setQuantity(rs.getInt(4));

				  
				        	report.setEngraveDate(rs.getTimestamp(5));
				  
				        	report.setEmployee(rs.getString(6));
				        	report.setReportSideA(rs.getInt(7));
				        	report.setReportSideB(rs.getInt(8));
				        	report.setEngraveID(rs.getInt(9));
				        	
				        	listOfReports.add(report);
				        	
				        	reportsCounter++;
				        	
				        }
				        
				        listOfReports.set(0, reportsCounter);
				        
		    		  
		    		
		    		  //return valid and the new list
				        response.add("Valid");
				        response.add(listOfReports);
				        rs.close();
		    	  }
		      }
		      
		      stmt.close();
		      stmt2.close();
		      stmt3.close();
		      rs2.close();
		} 
		catch (SQLException e)
		{
			response.add("SQL Error");
	//e.printStackTrace();
		}
        

    	  
		
		
		


		return response;
	}


/**
 * 
 * @param scheduleID key string - month and year
 * @param engraveSchedule schedule for engrave
 * @param polishSchedule schedule for polish
 * @param qcSchedule schedule for qc
 * @param packagingSchedule schedule for pakcaging
 * @return validity string
 */
	public ArrayList<Object> createNewSchedule(String scheduleID ,ArrayList<String> engraveSchedule,
			ArrayList<String> polishSchedule, ArrayList<String> qcSchedule,
			ArrayList<String> packagingSchedule)
			{

		ArrayList<Object> response = new ArrayList<Object>();
		
		try
		{
			Statement stmt= conn.createStatement();

			
			int scheduleIndex = Integer.parseInt(scheduleID)*100 +1; // now in the date format of yyyyMM01 for the selected schedule
			int tempSchedule;
						
			// For each station, create a loop setting targets
	
			// engrave
			tempSchedule = scheduleIndex;
			for (int i = 0 ; i <= 30 ; i++)
			{
				stmt.executeUpdate("REPLACE INTO planningschedule (scheduleID, station, target) VALUES('" + tempSchedule + "','engrave','" + engraveSchedule.get(i).toString() + "');");
				tempSchedule++;
			}
			
						
			// polish
			tempSchedule = scheduleIndex;
			for (int i = 0 ; i <= 30 ; i++)
			{
				stmt.executeUpdate("REPLACE INTO planningschedule (scheduleID, station, target) VALUES('" + tempSchedule + "','polish','" + polishSchedule.get(i).toString() + "');");
				tempSchedule++;
			}
			
			// qc
			tempSchedule = scheduleIndex;
			for (int i = 0 ; i <= 30 ; i++)
			{
				stmt.executeUpdate("REPLACE INTO planningschedule (scheduleID, station, target) VALUES('" + tempSchedule + "','qc','" + qcSchedule.get(i).toString() + "');");
				tempSchedule++;
			}
			
			// packaging
			tempSchedule = scheduleIndex;
			for (int i = 0 ; i <= 30 ; i++)
			{
				stmt.executeUpdate("REPLACE INTO planningschedule (scheduleID, station, target) VALUES('" + tempSchedule + "','packaging','" + packagingSchedule.get(i).toString() + "');");
				tempSchedule++;
			}
			

			response.add("valid");
			
			stmt.close();

		}
		catch (SQLException e) 
		{
			response.add("SQL Error");
		
			//e.printStackTrace();
		}
		
		
		
		return response;
	}
	

	
/**
 * 
 * @param scheduleID month and year
 * @return schedule for month chosen
 */
	public ArrayList<Object> getSchedule(String scheduleID)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		
		String startDate = scheduleID + "01";
		String endDate = scheduleID + "31";
				
		ArrayList<Object> engrave = new ArrayList<Object>();
		ArrayList<Object> polish = new ArrayList<Object>();
		ArrayList<Object> qc = new ArrayList<Object>();
		ArrayList<Object> packaging = new ArrayList<Object>();
		
		boolean engraveSchedule;
		boolean polishSchedule;
		boolean qcSchedule;
		boolean packagingSchedule;
		
		
		response.add("placeHolder");// index 0
		response.add(engrave); // index 1
		response.add(polish); // index 2
		response.add(qc); // index 3
		response.add(packaging); // index 4
		
				
		try 
		{
			Statement stmt= conn.createStatement();
			Statement stmt2= conn.createStatement();
			Statement stmt3= conn.createStatement();
			Statement stmt4= conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT target FROM planningschedule WHERE station = 'engrave' AND scheduleID >= '" + startDate + "' AND scheduleID <= '" + endDate + "';");
			ResultSet rs2 = stmt2.executeQuery("SELECT target FROM planningschedule WHERE station = 'polish' AND scheduleID >= '" + startDate + "' AND scheduleID <= '" + endDate + "';");
			ResultSet rs3 = stmt3.executeQuery("SELECT target FROM planningschedule WHERE station = 'qc' AND scheduleID >= '" + startDate + "' AND scheduleID <= '" + endDate + "';");
			ResultSet rs4 = stmt4.executeQuery("SELECT target FROM planningschedule WHERE station = 'packaging' AND scheduleID >= '" + startDate + "' AND scheduleID <= '" + endDate + "';");
			
			
			if (engraveSchedule=rs.next() )
			{
				((ArrayList<Object>)response.get(1)).add("valid");
				
				rs.beforeFirst(); // restore place
				

								
				while (rs.next())
				{
						((ArrayList<Object>)response.get(1)).add(rs.getString(1));
					
				}
				
			}
			else
			{
				// no schedule for the date
				((ArrayList<Object>)response.get(1)).add("No schedule");
			}
			
			
			if (polishSchedule=rs2.next() )
			{
				((ArrayList<Object>)response.get(2)).add("valid");
				
				rs2.beforeFirst(); // restore place
				

								
				while (rs2.next())
				{
						((ArrayList<Object>)response.get(2)).add(rs2.getString(1));
					
				}
				
			}
			else
			{
				// no schedule for the date
				((ArrayList<Object>)response.get(2)).add("No schedule");
			}
			
			
			if (qcSchedule=rs3.next() )
			{
				((ArrayList<Object>)response.get(3)).add("valid");
				
				rs3.beforeFirst(); // restore place
				

								
				while (rs3.next())
				{
						((ArrayList<Object>)response.get(3)).add(rs3.getString(1));
					
				}
				
			}
			else
			{
				// no schedule for the date
				((ArrayList<Object>)response.get(3)).add("No schedule");
			}
			
			
			if (packagingSchedule=rs4.next() )
			{
				((ArrayList<Object>)response.get(4)).add("valid");
				
				rs4.beforeFirst(); // restore place
				

								
				while (rs4.next())
				{
						((ArrayList<Object>)response.get(4)).add(rs4.getString(1));
					
				}
				
			}
			else
			{
				// no schedule for the date
				((ArrayList<Object>)response.get(4)).add("No schedule");
			}
			
			
			
		
			
			
			
			stmt.close();
			rs.close();
			stmt2.close();
			rs2.close();
			stmt3.close();
			rs3.close();
			stmt4.close();
			rs4.close();
			
			if (engraveSchedule || polishSchedule || qcSchedule || packagingSchedule)
				response.set(0, "valid");
			else
				response.set(0, "no schedule");
			
		}
		catch (SQLException e) 
		{
			response.set(0, "SQL Error"); // index 0
		
			//e.printStackTrace();
		}
		
	
		return response;
	}
	
	/**
	 * 
	 * @param startTimestamp begin date
	 * @param endTimestamp end date
	 * @param yesterdayTimestamp yesterday's date
	 * @return data for VPOP station
	 */
	public ArrayList<Object> initializeAllFieldsVPOfOperations(Timestamp startTimestamp, Timestamp endTimestamp, Timestamp yesterdayTimestamp)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		
		// get data to initialize dashbaord
		response.add("valid");
		System.out.println("Got to initializeAllFieldsVPOfOperations");
		
		ArrayList<Object> dashboard = valuesForDashboard(startTimestamp, endTimestamp,yesterdayTimestamp);
		response.add(dashboard);
		// space for other calls
		
		
		
	
		return response;
	}

	
		
	/**
	 * 
	 * @param startTimestamp begin date
	 * @param endTimestamp end date
	 * @param yesterdayTimestamp yesterday
	 * @return values for dashboard
	 */
	public ArrayList<Object> valuesForDashboard(Timestamp startTimestamp, Timestamp endTimestamp, Timestamp yesterdayTimestamp)
	{
		
		ArrayList<Object> dashboardElementValues = new ArrayList<Object>();
		
		dashboardElementValues.add("GaugesPlaceHolder");
		dashboardElementValues.add("WipPlacHolder");
		dashboardElementValues.add("FailReportPlaceHolder");
		
		// get data for the dashboard gauges
		ArrayList<Object> gaugesData = gaugesData(startTimestamp,endTimestamp,yesterdayTimestamp);
		dashboardElementValues.set(0, gaugesData.get(0).toString()); // index 0, valid or sql error strings.
		dashboardElementValues.add(gaugesData); // index 3 = arraylist<object> with the data for the gauges
				
		// WIP:
		//get count orders, regardless of dates, with the corresponding statusIDs
		ArrayList<Object> wip = getWIP(startTimestamp,endTimestamp);
		dashboardElementValues.set(1, wip.get(0).toString()); // index 1, valid or sql error strings.
		dashboardElementValues.add(wip); // index 4 = arraylist<object> with the WIP 
		
		// Quality:
		// Get quality reports from yesterday and Between the selected dates
		ArrayList<Object> failReport = getFailReport(startTimestamp,endTimestamp,yesterdayTimestamp);
		dashboardElementValues.set(2, failReport.get(0).toString()); // index 2, valid or sql error strings.
		dashboardElementValues.add(failReport);// index 5, arraylist<object> with the failreport
		
		
	
		return dashboardElementValues;
	}


	

/**
 * 
 * @param startTimestamp begin date
 * @param endTimestamp end date
 * @param yesterdayTimestamp yesterday
 * @return data for dashboard fail reports
 */
	public ArrayList<Object> getFailReport(Timestamp startTimestamp,Timestamp endTimestamp, Timestamp yesterdayTimestamp)
	{
		// Get totals and fails, for every product type
		ArrayList<Object> response = new ArrayList<Object>();
		
		response.add("valid");
		
		try
		{

			// get fail reports for time period
			String sqlQuery1 = "SELECT orders.type,SUM(orders.quantity),SUM(qualitycontrol.failSetFlag) FROM orders,qualitycontrol WHERE orders.orderID = qualitycontrol.orderID AND qualitycontrol.qcDate >= ? AND qualitycontrol.qcDate <= ? GROUP BY orders.type;";
			PreparedStatement preparedStatement1 = conn.prepareStatement(sqlQuery1);
		    preparedStatement1.setTimestamp(1, startTimestamp);
		    preparedStatement1.setTimestamp(2, endTimestamp);
		    
			ResultSet rs1 = preparedStatement1.executeQuery(); 
			
			FailReport periodReport = new FailReport();
			
			while(rs1.next())
			{
				switch (rs1.getString(1).toUpperCase())
				{
				case "ABB3":	periodReport.setABB3Fails(rs1.getInt(3));
							 	periodReport.setABB3Total(rs1.getInt(2));
							 	break;
							 
				case "ANCHOR": 	periodReport.setANCHORFails(rs1.getInt(3));
								periodReport.setANCHORTotal(rs1.getInt(2));
								break;
								
				case "BAL15": 	periodReport.setBAL15Fails(rs1.getInt(3));
								periodReport.setBAL15Total(rs1.getInt(2));
								break;
								
				case "BAL65":	periodReport.setBAL65Fails(rs1.getInt(3));
								periodReport.setBAL65Total(rs1.getInt(2));
								break;
				case "BAL-65":	periodReport.setBAL65Fails(rs1.getInt(3));
								periodReport.setBAL65Total(rs1.getInt(2));
								break;
								
				case "OPAB130":	periodReport.setOPAB130Fails(rs1.getInt(3));
								periodReport.setOPAB130Total(rs1.getInt(2));
								break;
								
				case "OPAB135":	periodReport.setOPAB135Fails(rs1.getInt(3));
								periodReport.setOPAB135Total(rs1.getInt(2));
								break;		
								
				case "OPAB16":	periodReport.setOPAB16Fails(rs1.getInt(3));
								periodReport.setOPAB16Total(rs1.getInt(2));
								break;	
								
				case "OPAB19":	periodReport.setOPAB19Fails(rs1.getInt(3));
								periodReport.setOPAB19Total(rs1.getInt(2));
								break;	
								
				case "RING2":	periodReport.setRING2Fails(rs1.getInt(3));
								periodReport.setRING2Total(rs1.getInt(2));
								break;	
								
				case "RING":	periodReport.setRING2Fails(rs1.getInt(3));
								periodReport.setRING2Total(rs1.getInt(2));
								break;	
						
				case "BLENS":	periodReport.setBLENSFails(rs1.getInt(3));
								periodReport.setBLENSTotal(rs1.getInt(2));
								break;	
						
				case "BUNNYLENS":	periodReport.setBUNNYLENSFails(rs1.getInt(3));
									periodReport.setBUNNYLENSTotal(rs1.getInt(2));
									break;	
									
				case "BNYLENS":	periodReport.setBUNNYLENSFails(rs1.getInt(3));
									periodReport.setBUNNYLENSTotal(rs1.getInt(2));
									break;	
									
				case "SEELENS":		periodReport.setSEELENSFails(rs1.getInt(3));
									periodReport.setSEELENSTotal(rs1.getInt(2));
									break;	
											
				case "SEELENSAF":	periodReport.setSEELENSAFFails(rs1.getInt(3));
									periodReport.setSEELENSAFTotal(rs1.getInt(2));
									break;	
											
				
				case "BNYAFEZ":		periodReport.setBNYAFEZFails(rs1.getInt(3));
									periodReport.setBNYAFEZTotal(rs1.getInt(2));
									break;	
						
				case "BNYAF":		periodReport.setBNYAFEZFails(rs1.getInt(3));
									periodReport.setBNYAFEZTotal(rs1.getInt(2));
									break;
									
									
				case "SLENSAFEZ":		periodReport.setSLENSAFEZFails(rs1.getInt(3));
										periodReport.setSLENSAFEZTotal(rs1.getInt(2));
										break;
										
				case "SLENSAF":		periodReport.setSLENSAFEZFails(rs1.getInt(3));
									periodReport.setSLENSAFEZTotal(rs1.getInt(2));
									break;	
				
				
				case "BNYMFEZ":		periodReport.setBNYMFEZFails(rs1.getInt(3));
									periodReport.setBNYMFEZTotal(rs1.getInt(2));
									break;
									
				case "BNYMF":		periodReport.setBNYMFEZFails(rs1.getInt(3));
									periodReport.setBNYMFEZTotal(rs1.getInt(2));
									break;
									
				case "SLENSMFEZ":		periodReport.setSLENSMFEZFails(rs1.getInt(3));
										periodReport.setSLENSMFEZTotal(rs1.getInt(2));
										break;
										
				case "SLENSMF":		periodReport.setSLENSMFEZFails(rs1.getInt(3));
									periodReport.setSLENSMFEZTotal(rs1.getInt(2));
									break;		
									
				case "SEELENSMF":		periodReport.setSEELENSMFFails(rs1.getInt(3));
										periodReport.setSEELENSMFTotal(rs1.getInt(2));
										break;	
				
				case "BUNNYMF":		periodReport.setBUNNYMFFails(rs1.getInt(3));
									periodReport.setBUNNYMFTotal(rs1.getInt(2));
									break;	
				
				case "BNYTR/VISTOR":		periodReport.setBNYTRVISTORFails(rs1.getInt(3));
											periodReport.setBNYTRVISTORTotal(rs1.getInt(2));
											break;
											
				case "BNYTR":		periodReport.setBNYTRVISTORFails(rs1.getInt(3));
									periodReport.setBNYTRVISTORTotal(rs1.getInt(2));
									break;
				
				case "VISTOR":		periodReport.setBNYTRVISTORFails(rs1.getInt(3));
									periodReport.setBNYTRVISTORTotal(rs1.getInt(2));
									break;
				
				
											
				case "SEELENSHP":		periodReport.setSEELENSHPFails(rs1.getInt(3));
										periodReport.setSEELENSHPTotal(rs1.getInt(2));
										break;
				
				case "SLENSHP":		periodReport.setSEELENSHPFails(rs1.getInt(3));
									periodReport.setSEELENSHPTotal(rs1.getInt(2));
									break;
				
										
				case "BUNNYHP":		periodReport.setBUNNYHPFails(rs1.getInt(3));
									periodReport.setBUNNYHPTotal(rs1.getInt(2));
									break;	
									
				case "BUNYHP":		periodReport.setBUNNYHPFails(rs1.getInt(3));
									periodReport.setBUNNYHPTotal(rs1.getInt(2));
									break;
				
				case "ETOSEELENSHP":		periodReport.setETOSEELENSHPFails(rs1.getInt(3));
											periodReport.setETOSEELENSHPTotal(rs1.getInt(2));
											break;	
				
				case "ETOBUNNYHP":		periodReport.setETOBUNNYHPFails(rs1.getInt(3));
										periodReport.setETOBUNNYHPTotal(rs1.getInt(2));
										break;
				
				case "PRESBIA":		periodReport.setPresbiaFails(rs1.getInt(3));
									periodReport.setPresbiaTotal(rs1.getInt(2));
									break;
				
						
									
				}
					
				
				
				
			}

			response.add(periodReport); // index 1
			
			// get fail reports for yesterday
			String sqlQuery2 = "SELECT orders.type,SUM(orders.quantity),SUM(qualitycontrol.failSetFlag) FROM orders,qualitycontrol WHERE orders.orderID = qualitycontrol.orderID AND qualitycontrol.qcDate = ? GROUP BY orders.type;";
			PreparedStatement preparedStatement2 = conn.prepareStatement(sqlQuery2);
		    preparedStatement2.setTimestamp(1, yesterdayTimestamp);
		    
			ResultSet rs2 = preparedStatement2.executeQuery(); 
			
			FailReport yesterdayReport = new FailReport();
			
			
			while(rs2.next())
			{
				switch (rs2.getString(1).toUpperCase())
				{
				case "ABB3":	yesterdayReport.setABB3Fails(rs2.getInt(3));
							 	yesterdayReport.setABB3Total(rs2.getInt(2));
							 	break;
							 
				case "ANCHOR": 	yesterdayReport.setANCHORFails(rs2.getInt(3));
								yesterdayReport.setANCHORTotal(rs2.getInt(2));
								break;
								
				case "BAL15": 	yesterdayReport.setBAL15Fails(rs2.getInt(3));
								yesterdayReport.setBAL15Total(rs2.getInt(2));
								break;
								
				case "BAL65":	yesterdayReport.setBAL65Fails(rs2.getInt(3));
								yesterdayReport.setBAL65Total(rs2.getInt(2));
								break;
				case "BAL-65":	yesterdayReport.setBAL65Fails(rs2.getInt(3));
								yesterdayReport.setBAL65Total(rs2.getInt(2));
								break;
								
				case "OPAB130":	yesterdayReport.setOPAB130Fails(rs2.getInt(3));
								yesterdayReport.setOPAB130Total(rs2.getInt(2));
								break;
								
				case "OPAB135":	yesterdayReport.setOPAB135Fails(rs2.getInt(3));
								yesterdayReport.setOPAB135Total(rs2.getInt(2));
								break;		
								
				case "OPAB16":	yesterdayReport.setOPAB16Fails(rs2.getInt(3));
								yesterdayReport.setOPAB16Total(rs2.getInt(2));
								break;	
								
				case "OPAB19":	yesterdayReport.setOPAB19Fails(rs2.getInt(3));
								yesterdayReport.setOPAB19Total(rs2.getInt(2));
								break;	
								
				case "RING2":	yesterdayReport.setRING2Fails(rs2.getInt(3));
								yesterdayReport.setRING2Total(rs2.getInt(2));
								break;	
								
				case "RING":	yesterdayReport.setRING2Fails(rs2.getInt(3));
								yesterdayReport.setRING2Total(rs2.getInt(2));
								break;	
						
				case "BLENS":	yesterdayReport.setBLENSFails(rs2.getInt(3));
								yesterdayReport.setBLENSTotal(rs2.getInt(2));
								break;	
						
				case "BUNNYLENS":	yesterdayReport.setBUNNYLENSFails(rs2.getInt(3));
									yesterdayReport.setBUNNYLENSTotal(rs2.getInt(2));
									break;	
									
				case "BNYLENS":	yesterdayReport.setBUNNYLENSFails(rs2.getInt(3));
									yesterdayReport.setBUNNYLENSTotal(rs2.getInt(2));
									break;	
									
				case "SEELENS":		yesterdayReport.setSEELENSFails(rs2.getInt(3));
									yesterdayReport.setSEELENSTotal(rs2.getInt(2));
									break;	
											
				case "SEELENSAF":	yesterdayReport.setSEELENSAFFails(rs2.getInt(3));
									yesterdayReport.setSEELENSAFTotal(rs2.getInt(2));
									break;	
											
				
				case "BNYAFEZ":		yesterdayReport.setBNYAFEZFails(rs2.getInt(3));
									yesterdayReport.setBNYAFEZTotal(rs2.getInt(2));
									break;	
									
				case "BNYAF":		yesterdayReport.setBNYAFEZFails(rs2.getInt(3));
									yesterdayReport.setBNYAFEZTotal(rs2.getInt(2));
									break;	
									
									
				case "SLENSAFEZ":		yesterdayReport.setSLENSAFEZFails(rs2.getInt(3));
										yesterdayReport.setSLENSAFEZTotal(rs2.getInt(2));
										break;
										
				case "SLENSAF":		yesterdayReport.setSLENSAFEZFails(rs2.getInt(3));
									yesterdayReport.setSLENSAFEZTotal(rs2.getInt(2));
									break;	
				
				case "BNYMFEZ":		yesterdayReport.setBNYMFEZFails(rs2.getInt(3));
									yesterdayReport.setBNYMFEZTotal(rs2.getInt(2));
									break;
									
				case "BNYMF":		yesterdayReport.setBNYMFEZFails(rs2.getInt(3));
									yesterdayReport.setBNYMFEZTotal(rs2.getInt(2));
									break;
									
				case "SLENSMFEZ":		yesterdayReport.setSLENSMFEZFails(rs2.getInt(3));
										yesterdayReport.setSLENSMFEZTotal(rs2.getInt(2));
										break;
										
				case "SLENSMF":		yesterdayReport.setSLENSMFEZFails(rs2.getInt(3));
									yesterdayReport.setSLENSMFEZTotal(rs2.getInt(2));
									break;					
									
				case "SEELENSMF":		yesterdayReport.setSEELENSMFFails(rs2.getInt(3));
										yesterdayReport.setSEELENSMFTotal(rs2.getInt(2));
										break;	
				
				case "BUNNYMF":		yesterdayReport.setBUNNYMFFails(rs2.getInt(3));
									yesterdayReport.setBUNNYMFTotal(rs2.getInt(2));
									break;	
				
				case "BNYTR/VISTOR":		yesterdayReport.setBNYTRVISTORFails(rs2.getInt(3));
											yesterdayReport.setBNYTRVISTORTotal(rs2.getInt(2));
											break;
											
				case "BNYTR":		yesterdayReport.setBNYTRVISTORFails(rs2.getInt(3));
									yesterdayReport.setBNYTRVISTORTotal(rs2.getInt(2));
									break;
				
				case "VISTOR":		yesterdayReport.setBNYTRVISTORFails(rs2.getInt(3));
									yesterdayReport.setBNYTRVISTORTotal(rs2.getInt(2));
									break;
				
				
				case "SEELENSHP":		yesterdayReport.setSEELENSHPFails(rs2.getInt(3));
										yesterdayReport.setSEELENSHPTotal(rs2.getInt(2));
										break;
										
				case "SLENSHP":		yesterdayReport.setSEELENSHPFails(rs2.getInt(3));
									yesterdayReport.setSEELENSHPTotal(rs2.getInt(2));
									break;
					
					
										
				case "BUNNYHP":		yesterdayReport.setBUNNYHPFails(rs2.getInt(3));
									yesterdayReport.setBUNNYHPTotal(rs2.getInt(2));
									break;
									
				case "BUNYHP":		yesterdayReport.setBUNNYHPFails(rs2.getInt(3));
									yesterdayReport.setBUNNYHPTotal(rs2.getInt(2));
									break;	
				
				case "ETOSEELENSHP":		yesterdayReport.setETOSEELENSHPFails(rs2.getInt(3));
											yesterdayReport.setETOSEELENSHPTotal(rs2.getInt(2));
											break;	
				
				case "ETOBUNNYHP":		yesterdayReport.setETOBUNNYHPFails(rs2.getInt(3));
										yesterdayReport.setETOBUNNYHPTotal(rs2.getInt(2));
										break;
				
				case "PRESBIA":		yesterdayReport.setPresbiaFails(rs2.getInt(3));
									yesterdayReport.setPresbiaTotal(rs2.getInt(2));
									break;
				
						
				}
	
			
			
			
			}
			
			response.add(yesterdayReport); // index 2
			

			preparedStatement1.close();
			rs1.close();
			preparedStatement2.close();
			rs2.close();
			
		}
		catch (SQLException e)
		{
			response.set(0, "SQL Error");
		
			//e.printStackTrace();
		}
		
	
		return response;
	}




/**
 * 
 * @param startTimestamp begin date
 * @param endTimestamp end date
 * @param yesterdayTimestamp yesterday
 * @return data values for gauges in the dashboard
 */
	public ArrayList<Object> gaugesData(Timestamp startTimestamp, Timestamp endTimestamp, Timestamp yesterdayTimestamp)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		response.add("PlaceHolder");
		// Gauges:
				
		String scheduleStart =  new SimpleDateFormat("yyyyMMdd").format(startTimestamp);
		String scheduleEnd = new SimpleDateFormat("yyyyMMdd").format(endTimestamp);
		
		
		DashboardGauges report = new DashboardGauges();
		
		//Get plans for the span of dates given and sum the target quantities, for each station
			try
			{
	
				Statement stmt = conn.createStatement();				
				ResultSet rs = stmt.executeQuery("SELECT station,SUM(target) FROM planningschedule where scheduleID >= '" + scheduleStart + "' AND scheduleID <= '" + scheduleEnd + "' GROUP BY station;");
				
				if (rs.next())
				{
					report.setScheduleExists(true);
					report.setEngraveTarget(rs.getInt(2));
					rs.next();
					report.setPackagingTarget(rs.getInt(2));
					rs.next();
					report.setPolishTarget(rs.getInt(2));
					rs.next();
					report.setQcTarget(rs.getInt(2));
					
				}
				else
				{
					// no schedule exists
					report.setScheduleExists(false);
				}
				response.set(0, "valid");
				stmt.close();
				rs.close();
				
			
			
				//----------------------- Get reports for the span of dates given and sum reported quantities

			// separation reports
		
				String sqlQuery1 = "SELECT SUM(orders.quantity) FROM orders,seperation WHERE orders.orderID = seperation.orderID AND seperation.seperationDate >= ? AND seperation.seperationDate <= ? ;";
			
				PreparedStatement preparedStatement1 = conn.prepareStatement(sqlQuery1);
			    preparedStatement1.setTimestamp(1, startTimestamp);
			    preparedStatement1.setTimestamp(2, endTimestamp);
				
				ResultSet rs1 = preparedStatement1.executeQuery(); 
				
				if (rs1.next())
					report.setEngraveReported(rs1.getInt(1));
				else
					report.setEngraveReported(0);
				preparedStatement1.close();
				rs1.close();
				
			
		
			
			// polish reports
			
				String sqlQuery2 = "SELECT SUM(orders.quantity) FROM orders,ordersinpolish,polish WHERE orders.orderID=ordersinpolish.orderID AND ordersinpolish.polishID = polish.polishID AND polish.endDate >= ? AND polish.endDate <= ? AND orders.statusID != 31;";
				PreparedStatement preparedStatement2 = conn.prepareStatement(sqlQuery2);
			    preparedStatement2.setTimestamp(1, startTimestamp);
			    preparedStatement2.setTimestamp(2, endTimestamp);
			    
				ResultSet rs2 = preparedStatement2.executeQuery(); 
				
				if (rs2.next())
					report.setPolishReported(rs2.getInt(1));
				else
					report.setPolishReported(0);
				preparedStatement2.close();
				rs2.close();
			
			
			
			
			
			// qc reports
			
				String sqlQuery3 = "SELECT SUM(orders.quantity) FROM orders,qualitycontrol WHERE orders.orderID=qualitycontrol.orderID AND  qualitycontrol.qcDate >= ? AND qualitycontrol.qcDate <= ?;";
				PreparedStatement preparedStatement3 = conn.prepareStatement(sqlQuery3);
			    preparedStatement3.setTimestamp(1, startTimestamp);
			    preparedStatement3.setTimestamp(2, endTimestamp);
			    
				ResultSet rs3 = preparedStatement3.executeQuery(); 
				
				if (rs3.next())
					report.setQcReported(rs3.getInt(1));
				else
					report.setQcReported(0);
				preparedStatement3.close();
				rs3.close();
				
			
	
			
					// packaging reports
			
			
				String sqlQuery4 = "SELECT SUM(packagereport.quantity) FROM packagereport WHERE packagereport.date >= ? AND packagereport.date <= ?;";
				PreparedStatement preparedStatement4 = conn.prepareStatement(sqlQuery4);
			    preparedStatement4.setTimestamp(1, startTimestamp);
			    preparedStatement4.setTimestamp(2, endTimestamp);
			    
				ResultSet rs4 = preparedStatement4.executeQuery(); 
				
				if (rs4.next())
					report.setPackagingReported(rs4.getInt(1));
				else
					report.setPackagingReported(0);
				preparedStatement4.close();
				rs4.close();
				
			
			
			
				// ------------------------ Get reports for yesterday.
			
					//engrave report
				
				String sqlQuery5 = "SELECT SUM(orders.quantity) FROM orders,seperation WHERE orders.orderID = seperation.orderID AND seperation.seperationDate = ?;";
				PreparedStatement preparedStatement5 = conn.prepareStatement(sqlQuery5);
			    preparedStatement5.setTimestamp(1, yesterdayTimestamp);
			    
				ResultSet rs5 = preparedStatement5.executeQuery(); 
				
				if (rs5.next())
					report.setYesterdayEngraveReported(rs5.getInt(1));
				else
					report.setYesterdayEngraveReported(0);
				preparedStatement5.close();
				rs5.close();
				
			
			
				// polish report
			
					
				String sqlQuery6 = "SELECT SUM(orders.quantity) FROM orders,ordersinpolish,polish WHERE orders.orderID=ordersinpolish.orderID AND ordersinpolish.polishID = polish.polishID AND polish.endDate = ? AND orders.statusID != 31;";
				PreparedStatement preparedStatement6 = conn.prepareStatement(sqlQuery6);
			    preparedStatement6.setTimestamp(1, yesterdayTimestamp);
			    
				ResultSet rs6 = preparedStatement6.executeQuery(); 
				
				if (rs6.next())
					report.setYesterdayPolishReported(rs6.getInt(1));
				else
					report.setYesterdayPolishReported(0);
				preparedStatement6.close();
				rs6.close();
				
			
			
			// qc report
			
					
				String sqlQuery7 = "SELECT SUM(orders.quantity) FROM orders,qualitycontrol WHERE orders.orderID = qualitycontrol.orderID AND qualitycontrol.qcDate = ?;";
				PreparedStatement preparedStatement7 = conn.prepareStatement(sqlQuery7);
			    preparedStatement7.setTimestamp(1, yesterdayTimestamp);
			    
				ResultSet rs7 = preparedStatement7.executeQuery(); 
				
				if (rs7.next())
					report.setYesterdayQCReported(rs7.getInt(1));
				else
					report.setYesterdayQCReported(0);
				preparedStatement7.close();
				rs7.close();
				
			
			
			// packaging report
			
					
				String sqlQuery8 = "SELECT SUM(packagereport.quantity) FROM packagereport WHERE packagereport.date = ?;";
				PreparedStatement preparedStatement8 = conn.prepareStatement(sqlQuery8);
			    preparedStatement8.setTimestamp(1, yesterdayTimestamp);
			    
				ResultSet rs8 = preparedStatement8.executeQuery(); 
				
				if (rs8.next())
					report.setYesterdayPackagingReported(rs8.getInt(1));
				else
					report.setYesterdayPackagingReported(0);
				preparedStatement8.close();
				rs8.close();
			
				response.set(0, "valid");
				
			}
				catch (SQLException e) 
			{
				response.set(0, "SQL Error");
				
				//e.printStackTrace();
			}
		
			
			response.add(report);
	
		return response;
	}

	
	/**
	 * 
	 * @param startTimestamp begin date
	 * @param endTimestamp end date
	 * @return data values for WIP quantities
	 */
	public ArrayList<Object> getWIP(Timestamp startTimestamp,Timestamp endTimestamp)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		response.add("Valid");
		
		WIP report = new WIP();

		try 
		{
			Statement stmt1 = conn.createStatement();
			Statement stmt2= conn.createStatement();
			Statement stmt3= conn.createStatement();
			Statement stmt4= conn.createStatement();
			Statement stmt5= conn.createStatement();
			Statement stmt6= conn.createStatement();
			Statement stmt7= conn.createStatement();
			Statement stmt31= conn.createStatement();
			
			
			// WIP Engraving
			ResultSet rs1 = stmt1.executeQuery("SELECT SUM(orders.quantity) FROM orders WHERE statusID = 1;");
			ResultSet rs2 = stmt2.executeQuery("SELECT SUM(orders.quantity) FROM orders WHERE statusID = 2;");
			
			//WIP Polish
			ResultSet rs3 = stmt3.executeQuery("SELECT SUM(orders.quantity) FROM orders WHERE statusID = 3");
			ResultSet rs31 = stmt31.executeQuery("SELECT SUM(orders.quantity) FROM orders WHERE statusID = 31");
			ResultSet rs5 = stmt5.executeQuery("SELECT SUM(orders.quantity) FROM orders WHERE statusID = 5");
			
			// WIP QC
			
			ResultSet rs4 = stmt4.executeQuery("SELECT SUM(orders.quantity) FROM orders WHERE statusID = 4;");
			ResultSet rs7 = stmt7.executeQuery("SELECT SUM(orders.quantity) FROM orders WHERE statusID = 7;");
			
			// WIP Packaging
			ResultSet rs6 = stmt6.executeQuery("SELECT SUM(orders.quantity) FROM orders WHERE statusID = 6;");
			
			if (rs1.next())
				report.setQuantityStatus1(rs1.getInt(1));
			else
				report.setQuantityStatus1(0);
			if (rs2.next())
				report.setQuantityStatus2(rs2.getInt(1));
			else
				report.setQuantityStatus2(0);
			if (rs3.next())
				report.setQuantityStatus3(rs3.getInt(1));
			else
				report.setQuantityStatus3(0);
			if (rs4.next())
				report.setQuantityStatus4(rs4.getInt(1));
			else
				report.setQuantityStatus4(0);
			if (rs5.next())
				report.setQuantityStatus5(rs5.getInt(1));
			else
				report.setQuantityStatus5(0);
			if (rs6.next())
				report.setQuantityStatus6(rs6.getInt(1));
			else
				report.setQuantityStatus6(0);
			if (rs7.next())
				report.setQuantityStatus7(rs7.getInt(1));
			else
				report.setQuantityStatus7(0);
			if (rs31.next())
				report.setQuantityStatus31(rs31.getInt(1));
			else
				report.setQuantityStatus31(0);
			
			
			
			
			stmt1.close();
			stmt2.close();
			stmt3.close();
			stmt4.close();
			stmt5.close();
			stmt6.close();
			stmt7.close();
			stmt31.close();
			rs1.close();
			rs2.close();
			rs3.close();
			rs4.close();
			rs5.close();
			rs6.close();
			rs7.close();
			rs31.close();
			
			response.add(report);
			
		} 
		catch (SQLException e) 
		{
			response.set(0, "SQL Error");
			
			//e.printStackTrace();
		}
		
		
		
		
	
		return response;
	}
	
	/**
	 * This function removes an orderID that was selected from a jar that already polishing \ or not , and sets his status back to 3 (malfunction - human error)
	 * steps : 1. table orders , sets statusID to 3.
			   2. table polish , removes the relevant row
			   3. table ordersinpolish , removes the relevant row
	 * @param message	contains : 	0:removeFromPolish , 1:jarID , 2:orderID 
	 * @return ArrayList  respond	0: removeFromPolish , 1:valud \ unvalid
	 */

	public ArrayList<Object> removeFromPolish(ArrayList<Object> message) { 
		
		Statement stmt;
		int result;
		ResultSet rs;
		String polishID;
		String jarID = message.get(1).toString();
		String orderID = message.get(2).toString();
		ArrayList<Object> respond = new ArrayList<>();
		respond.add("removeFromPolish");
		try {
			stmt = conn.createStatement();
			result = stmt.executeUpdate("update hanitdb.orders set statusID='3' where orderID = '" + orderID + "';");
			// the next query receives the polishID needed to be removed as step 2+3 about .
			rs=stmt.executeQuery("select polish.polishID from orders, jars ,polish , ordersinpolish where orders.statusID = '3' AND jars.jarID='"+jarID+"' AND polish.polishID=ordersinpolish.polishID AND jars.jarID=polish.jarID AND ordersinpolish.orderID=orders.orderID AND orders.orderID='"+orderID+"';");
			if(rs.next())
			{
					polishID = rs.getString(1).toString();
			result = stmt.executeUpdate("delete from polish where polishID ='"+polishID+"';");
			result = stmt.executeUpdate("delete from ordersinpolish where polishID ='"+polishID+"' AND orderID='"+orderID+"'");
			respond.add("valid");
			rs.close();
			stmt.close();
			}
			else
			{
				respond.add("unvalid");
				rs.close();
				stmt.close();
				return respond;
			}
		} catch (SQLException e) {
		
			respond.add("unvalid");
			//e.printStackTrace();
			return respond;
		}

		return respond;
	}

	/**
	 * This method searches for the orderID 
	 * @param messagea	0:searchOrderID , 1:orderID
	 * @return ArrayList  respond	returns statusID of the orderID
	 */
	public ArrayList<Object> searchOrderID(ArrayList<Object> messagea) {
		
		ArrayList<Object> message = new ArrayList<>();
		ArrayList<Object> respond = new ArrayList<>();
		Statement stmt;
		ResultSet rs;
		message = messagea;
		respond.add("searchOrderID");
		String orderID = message.get(1).toString();
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select statusID from orders where orderID='"+orderID+"'");
			if(rs.next())
			{
				respond.add("valid");
				respond.add(rs.getString(1));
				//System.out.println(respond.get(2).toString());
				rs.close();
				stmt.close();
				return respond;
			}
			else 
			{
				respond.add(1,"unvalid");
				rs.close();
				stmt.close();
				return respond;
			}
			
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add(1,"unvalid");
			return respond;
		}
		
	}

	/**
	 * This method returns the orderID's within the jar that was selected .
	 * @param messagea	0:getJarContents , 1:jarID
	 * @return ArrayList respond		0:getJarContents , 1:valid \ unvalid , 2: orderID , 3:quantity 
	 */
	public ArrayList<Object> getJarContents(ArrayList<Object> messagea) {
		
		ArrayList<Object>message = new ArrayList<>();
		ArrayList<Object>respond = new ArrayList<>();
		message = messagea;
		Statement stmt;
		ResultSet rs;
		respond.add("getJarContents");
		String jarID = message.get(1).toString();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select orders.orderID,orders.quantity from jars , orders , ordersinpolish , polish where jars.jarID=polish.jarID AND orders.orderID=ordersinpolish.orderID AND polish.polishID=ordersinpolish.polishID AND jars.jarID='"+jarID+"' AND orders.statusID='31'; ");
				while(rs.next())
				{
					respond.add(rs.getString(1));
					respond.add(rs.getString(2));
				}
			if(respond.size()>1)
			{
				respond.add(1,"valid");
				rs.close();
				stmt.close();
				return respond;
			}
			else{
				respond.add(1,"unvalid");
				rs.close();
				stmt.close();
				return respond;
			}
		} catch (SQLException e) {
		
			//e.printStackTrace();
			respond.add(1,"unvalid");
			return respond;
		}

	}

	
	/**
	 * This method sets the orderID status to whatever we send to it and by orderID we send to it .
	 * @param message1	0:setStatusIDByOrderID , 1:orderID , 2:status sent could be any number 
	 * @return
	 */
	public ArrayList<Object> setStatusIDByOrderID(ArrayList<Object> message1) {
		
		ArrayList<Object>message = message1;
		ArrayList<Object>respond = new ArrayList<>();
		Statement stmt;
		int result;
		respond.add("setStatusIDByOrderID");
		try{
			stmt = conn.createStatement();
			result = stmt.executeUpdate("update hanitdb.orders set statusID='"+message.get(2)+"' where orderID = '" + message.get(1) + "';");
			if(result==1)
			{
				respond.add(1,"valid");
				stmt.close();
				return respond;
			}
			else{
				respond.add(1,"unvalid");
				stmt.close();
				return respond;
			}
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add(1,"unvalid");
			return respond;
		}
		
	}
/**
 *This method searches for polishID with jarID given and orderID given as well and returns polishID 
 * @param message1	0:getPolishID , 1:jarID , 2:orderID
 * @return	ArrayList respond	0:getPolishID , 1:valid \ unvalid , 2:polishID
 */

	public ArrayList<Object> getPolishID(ArrayList<Object> message1) {
		
		ArrayList<Object>message = message1;
		ArrayList<Object>respond = new ArrayList<>();
		Statement stmt;
		ResultSet rs;
		respond.add("getPolishID");
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select polish.polishID from polish , ordersinpolish  where polish.jarID='"+message.get(1)+"' AND ordersinpolish.orderID= '"+message.get(2)+"' AND ordersinpolish.polishID = polish.polishID order by polishID DESC limit 1;	");
			if(rs.next())
			{
				respond.add("valid");
				respond.add(rs.getString(1));
				rs.close();
				stmt.close();
				return respond;
			}
			else{
				respond.add(1,"unvalid");
				rs.close();
				stmt.close();
				return respond;
			}
			
		}catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add(1,"unvalid");
			return respond;
		}

	}
/**
 * This method updated the endDate in the polish table to the current date that the user pressed the button remove from polish .
 * @param message1	0:setEndDateInPolishByPolishID , 1:polishID
 * @return	ArrayList respond 0:setEndDateInPolishByPolishID , 1:valid \ unvalid
 */

	public ArrayList<Object> setEndDateInPolishByPolishID(ArrayList<Object> message1) {
		ArrayList<Object>message = message1;
		ArrayList<Object>respond = new ArrayList<>();
		Statement stmt;
		int result;
		Calendar cal = Calendar.getInstance();
		Date today = new Date(cal.getTimeInMillis());
		String todayStr = today.toString();
		respond.add("setEndDateInPolishByPolishID");
		try{
			stmt = conn.createStatement();
			result = stmt.executeUpdate("update hanitdb.polish set endDate = '"+todayStr+"'	where polishID = '"+message.get(1)+"'; ");
			if(result==1)
			{
				respond.add("valid");
				stmt.close();
				return respond;
			}else{
				respond.add("unvalid");
				stmt.close();
				return respond;
			}
		}catch(SQLException e){
			//e.printStackTrace();
			respond.add("unvalid");
			return respond;
		}

	}
/**
 *This method resets the pelletsCycles to 0 when the user askes to . 
 * @param message1	0:replacePellets , 1:jarID
 * @return	ArrayList respond	0:replacePellets , 1:valid \ unvalid 
 */

	public ArrayList<Object> replacePellets(ArrayList<Object> message1) {
		Statement stmt;
		int result;
		ArrayList<Object>message ;
		ArrayList<Object>respond = new ArrayList<>();
		
		message = message1;
		respond.add("replacePellets");
		try{
			stmt = conn.createStatement();
			result = stmt.executeUpdate("update hanitdb.jars set pelletesCycles = '0' where jars.jarID ='"+message.get(1)+"';");
			if(result==1)
			{
				respond.add("valid");
				stmt.close();
				return respond;
			}
			else 
			{
				respond.add("unvalid");
				stmt.close();
				return respond;
			}
		}catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add("unvalid");
			return respond ;
		}
	}
/**
 * Updates the polish table at the attribute end date with the new end date after we added extra time .
 * @param message1	0:updateExtraDays , 1:jarID , 2:daysToAdd , 3:originalendDate 
 * @return	ArrayList respond	0:updateExtraDays , 1:valid \ unvalid .
 */

	public ArrayList<Object> updateExtraDays(ArrayList<Object> message1) {
		Statement stmt,stmt1;
		ResultSet rs;
		int result,daysToAddINT;
		String jarID , daysToAdd ,originalendDate ,str=null;;
		Date tempOrig,tempEnd;
		DateFormat dateFormate = new SimpleDateFormat("YYYY/MM/DD");
		ArrayList<Object>message ;
		ArrayList<Object>respond = new ArrayList<>();
		message = message1;
		jarID = message.get(1).toString();
		daysToAdd = message.get(2).toString();
		originalendDate = message.get(3).toString();
		tempOrig = Date.valueOf(originalendDate);
		daysToAddINT = Integer.valueOf(daysToAdd);
		Calendar calOrig = Calendar.getInstance();
		calOrig.setTime(tempOrig);
		calOrig.add(Calendar.DATE, daysToAddINT);
		tempEnd = new Date(calOrig.getTimeInMillis());
		//System.out.println(tempEnd);
		respond.add("updateExtraDays");
		try{
			stmt = conn.createStatement();
			//צריך להחזיר את מספר הפוליש כדי לדעת לקבוע את התאריך סיום פוליש
			rs = stmt.executeQuery("select polish.polishID from hanitdb.polish , hanitdb.ordersinpolish , hanitdb.jars , orders where polish.jarID=jars.jarID AND polish.polishID=ordersinpolish.polishID AND orders.orderID=ordersinpolish.orderID AND orders.statusID='31' AND polish.jarID= '"+jarID+"'");
			while(rs.next())
			{
				str = rs.getString(1); //str will hold the polishID
				jarID = message.get(1).toString();
				daysToAdd = message.get(2).toString();
				stmt1 = conn.createStatement();
				result = stmt1.executeUpdate("update hanitdb.polish set polish.endDate = '"+tempEnd+"' where polishID = '"+str+"'; ");
				//stmt.close();
				if(result == 1)
				{
					respond.add("valid");
					stmt.close();
					return respond;
				}
				else{
					respond.add("unvalid");
					stmt.close();
					return respond;
				}
			}
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add("unvalid");
			return respond;
		}
		respond.add("unvalid");
		return respond;

	}
/**
 * This function returns the orderID's which on status 6 - Packaging startup for the main panel .
 * @param message1	0:initializePackageMailPanelVariables , 1:
 * @return	ArrayList respond	0:initializePackageMailPanelVariables , 1: valid \ unvalid , 2:orderID
 */
	
	public ArrayList<Object> initializePackageMailPanelVariables(ArrayList<Object> message1) {
		Statement stmt;
		ResultSet rs;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		respond.add("initializePackageMailPanelVariables");
		try
		{
			stmt = conn.createStatement();
			rs= stmt.executeQuery("select orderID from orders where statusID = '6' OR statusID='81'");
			while(rs.next())
			{
				respond.add(rs.getString(1).toString());
			}
			if(respond.size()>1)
			{
				respond.add(1,"valid");
				return respond;
			}
			else{
				respond.add(1,"unvalid");
				return respond;
			}
		}catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add(1,"unvalid");
			return respond;
		}
		

	}

/**
 * 
 * @return data for QC GUi
 */
	public ArrayList<Object> initializeAllFieldsQualityControl() 
	{
		//ArrayList<Object> response = new ArrayList<Object>();
	  //  response.add("PlaceHolder");
		
		ArrayList<Object> listOfOrders = new ArrayList<Object>();
        int counter = 0;
        listOfOrders.add(counter);
   
        try
        {
        	
	        Statement stmt= conn.createStatement();
	  //    //System.out.println("SELECT orders.orderID, orders.type, orders.diopter, DATE_FORMAT(orders.orderdate,'%d/%m/%Y'), orders.quantity FROM orders WHERE orders.statusID = 4 OR orders.statusID = 7 ORDER BY orders.orderdate ASC;");
	        ResultSet rs = stmt.executeQuery("SELECT orders.orderID, orders.type, orders.diopter, DATE_FORMAT(orders.orderdate,'%d/%m/%Y'), orders.quantity FROM orders WHERE orders.statusID = 4 OR orders.statusID = 7 ORDER BY orders.orderdate ASC;");
	       
	   
	        	        
	        while(rs.next())
	        {
	        	ProductionOrder order = new ProductionOrder();
	        	order.setOrderID(rs.getInt(1));
	        	order.setType(rs.getString(2));
	        	order.setDiopter(Float.valueOf(rs.getString(3)));
	        	order.setDateAsString(rs.getString(4));
	        	order.setQuantity(rs.getInt(5));

         //       //System.out.println("+++Adding " + order.getOrderID());
                listOfOrders.add(order);
                counter++;
	        }

	        
	        
	        
	        
	        stmt.close();
	        rs.close();
	        
	        listOfOrders.set(0,counter);
  
	        
	        
        }
        catch (SQLException e)
        {
	      
        	listOfOrders.add("SQL Failed");
        	
	         //e.printStackTrace();
        }
        if (counter==0)
        	listOfOrders.add("Empty List");
        
     
        return listOfOrders;
	}

	/**
	 * Returns the actual pelletsCycle number of jarID given
	 * @param message1	0:getPelletsCyclesNumber , 1:jarID
	 * @return	ArrayList respond	0:getPelletsCyclesNumber , 1:valid \ unvalid , 2:pelletesCycles
	 */
	public ArrayList<Object> getPelletsCyclesNumber(ArrayList<Object> message1) {
		Statement stmt;
		ResultSet rs;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		respond.add("getPelletsCyclesNumber");
		try{
			stmt = conn.createStatement();
			rs=stmt.executeQuery("select pelletesCycles from jars where jarID='"+message.get(1).toString()+"'");
			while(rs.next())
			{
				respond.add(1,"valid");
				respond.add(rs.getString(1).toString());
				return respond;
			}
		}catch(SQLException e){
			//e.printStackTrace();
			respond.add(1,"unvalid");
			return respond;
		}
	
		respond.add(1,"unvalid");
		return respond;
	}

	/**
	 * Updates pelletsCycle number to 0 when the user wants to make it 0 .
	 * @param message1	0:updatePelletsCycles , 1:jarID
	 * @return	ArrayList respond	0:updatePelletsCycles , 1:valid \ unvalid 
	 */
	public ArrayList<Object> updatePelletsCycles(ArrayList<Object> message1) {
		Statement stmt = null,stmt1 = null;
		ResultSet rs;
		String pelletesCycles=null;
		int pelletesCyclesINT=0,result=0;
		ArrayList<Object>message=message1 ;
		String jarID=message.get(1).toString();
		ArrayList<Object>respond = new ArrayList<>();
		respond.add("updatePelletsCycles");
				pelletesCycles = message.get(2).toString();
				pelletesCyclesINT = Integer.valueOf(pelletesCycles);
				pelletesCyclesINT++;
				pelletesCycles = String.valueOf(pelletesCyclesINT).toString();
		try{
			stmt1 = conn.createStatement();
			result = stmt1.executeUpdate("update hanitdb.jars set jars.pelletesCycles = '"+pelletesCycles+"' where jarID='"+jarID+"';");
										//update hanitdb.polish set polish.endDate = '"+tempEnd+"' where polishID = '"+str+"'; ");
			//System.out.println("koko");
			respond.add(1,"valid");
			return respond;
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			//System.out.println("ERROR - src->server -> mySQL -> addNewPolish() : couldnt retrieve jar pellets number ");
			respond.add(1,"unvalid");
			return respond;
		}
		
	}

/**
 * 
 * @param qcReport quality control report data
 * @return validity string
 */
	public ArrayList<Object> addNewQCReport(QCReport qcReport) 
	{
		ArrayList<Object> response = new ArrayList<Object>();
		response.add("PlaceHolder");// index 0
		
		int failsetFlag = qcReport.getFailSetFlag();
		int repolishFlag = qcReport.getRepolishSetFlag();
		int resultOfCreateReport;
		int resultOfInsertOrder;		
		
	
		

		try
		{
			Statement stmt = conn.createStatement();
			Statement stmt2 = conn.createStatement();
			Statement stmt3 = conn.createStatement();
			Statement stmt4 = conn.createStatement();
			Statement stmt5 = conn.createStatement();
			
			// insert report
			stmt.executeUpdate("INSERT IGNORE INTO qualitycontrol(qcDate,orderID,sterilizationID,failSetFlag,repolishSetFlag,comments,deviationCountPlus05,deviationCountMinus05,deviationCountPlus10,deviationCountMinus10,deviationCountElse) VALUES('" + qcReport.getQcDate() + "','" + qcReport.getOrderID() +"','" + qcReport.getSterilizationID() + "','" + failsetFlag + "','" + repolishFlag +  "','" + qcReport.getFailset().getComments() +  "','" + qcReport.getDeviationCountPlus05() +  "','" + qcReport.getDeviationCountMinus05() +  "','" + qcReport.getDeviationCountPlus10() +  "','" + qcReport.getDeviationCountMinus10() +  "','" + qcReport.getDeviationCountElse() + "');");
			response.set(0, "Valid");
			
			// set orderID status
			stmt5.executeUpdate("UPDATE orders SET `statusID`='6' WHERE `orderID`='" + qcReport.getOrderID() + "';");
			
			// insert failset
			if (failsetFlag != 0)
			{
				FailSet failset = qcReport.getFailset();
				stmt2.executeUpdate("INSERT IGNORE INTO failsets(orderID,scratches,stuckChip,engraveMarks,aoptic,diopterDeviation,surface,dirt,dropedOrLost,bumps,hydration,loops,fractures,milling,asymmetry,characteristics,orange,c1TurnLens,c2ChippedEdge,c3CuttingChips,c4Scratches,c5Prism,c6FoldedLens,c7OODDiameter,c8OODThickness,c9Lost,c10LatheMarks,c11ChippedHole,c12NoHole,c13UndefinedParticles,c14FoggyResolution,c15OODDiopterPower,c16OODPlanoPower,engravingStationEngraving,engravingStationCharacteristics,engravingStationDroppedOrLost,engravePlan,dryFails) VALUES('" + failset.getOrderID() + "','" + failset.getScratches() +"','" + failset.getStuckChip() + "','" + failset.getEngraveMarks() + "','" + failset.getAoptic() +  "','" + failset.getDiopterDeviation() +  "','" + failset.getSurface() +  "','" + failset.getDirt() +  "','" + failset.getDropedOrLost() +  "','" + failset.getBumps() +  "','" + failset.getHydration() +  "','" + failset.getLoops() +  "','" + failset.getFractures() +  "','" + failset.getMilling() +  "','" + failset.getAsymmetry() +  "','" + failset.getCharacteristics() +  "','" +failset.getOrange() + "','" +failset.getC11ChippedHole() +  "','" +failset.getC2ChippedEdge() +  "','" +failset.getC3CuttingChips() +  "','" +failset.getC4Scratches() +  "','" +failset.getC5Prism() +  "','" +failset.getC6FoldedLens() +  "','" +failset.getC7OODDiameter() +  "','" +failset.getC8OODThickness() +  "','" +failset.getC9Lost() +  "','" +failset.getC10LatheMarks() +  "','" +failset.getC11ChippedHole() +  "','" +failset.getC12NoHole() +  "','" +failset.getC13UndefinedParticles() +  "','" +failset.getC14FoggyResolution() +  "','" +failset.getC15OODDiopterPower() +  "','" + failset.getC16OODPlanoPower() + "','" + failset.getEngravingStationEngraving() + "','" + failset.getEngravingStationCharacteristics() + "','" + failset.getEngravingStationDroppedOrLost() + "','" + failset.getEngravePlan() + "','" + failset.getDryFails() + "');");
				response.add("FailSet Valid"); // index 1
			}
			else
				response.add("No FailSet"); // index 1
			
			
			// insert repolish
			if (repolishFlag != 0)
			{
				RepolishSet repolishset = qcReport.getRepolishset();
				
				stmt3.executeUpdate("INSERT IGNORE INTO repolishsets(originalOrderID,repolishOrderID,pudra,engraveMarks,scratches,stuckChip) VALUES('" + repolishset.getOriginalOrderID() + "','" + repolishset.getRepolishOrderID() +"','" + repolishset.getPudra() + "','" + repolishset.getEngraveMarks() + "','" + repolishset.getScratches() +  "','" + repolishset.getStuckChip() + "');");
				response.add("Repolish Valid"); // index 2
				
				// create new order
				ProductionOrder repolishOrder = qcReport.getRepolishset().getRepolishOrder();
				resultOfInsertOrder = stmt.executeUpdate("INSERT IGNORE INTO orders(orderID,type,diopter,orderDate,quantity,statusID) VALUES('" + repolishOrder.getOrderID() + "','" + repolishOrder.getType() +"','" + repolishOrder.getDiopter() + "','" + repolishOrder.getOrderDate() + "','" + repolishOrder.getQuantity() +  "','" + repolishOrder.getStatusID() + "');");
				
				
				
				if (resultOfInsertOrder==1)
				{
					//valid
					response.add("New Order Valid"); // index 3
					
				}
				else
				{
					//failed
					response.add("Failed in New Order"); // index 3
				}
			}
			else
				response.add("No RepolishSet"); // index 2
			
			stmt.close();
			stmt2.close();
			stmt3.close();
			stmt4.close();
			stmt5.close();
			
		} 
		catch (SQLException e) 
		{
			response.set(0, "SQL Error"); // index 0
			
			//e.printStackTrace();
		}
		
		
		
		return response;
	}
	
	
/**
 *This method is used by GUI - MyInfoPanel to get polishID in order to insert comments by users. 
 * @param message1 0:updateCommentsIntoPolishRow , 1:jarID , 2:orderID , 3:comments
 * @return ArrayList message	0:updateCommentsIntoPolishRow , 1:valid \ unvalid 
 */

	public ArrayList<Object> updateCommentsIntoPolishRow(ArrayList<Object> message1) {
		
		Statement stmt = null,stmt1 = null;
		ResultSet rs;	
		int result;
		ArrayList<Object>message=message1 ;
		String jarID=message.get(1).toString();
		String orderID = message.get(2).toString();
		String comments = message.get(3).toString();
		ArrayList<Object>respond = new ArrayList<>();
		respond.add("updateCommentsIntoPolishRow");
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select polish.polishID from polish , orders , ordersinpolish where orders.orderID='"+orderID+"' AND orders.orderID = ordersinpolish.orderID AND polish.polishID=ordersinpolish.polishID AND polish.jarID='"+jarID+"' AND orders.statusID='31';");
			if(rs.next())
			{
				respond.add(1,"valid");
				respond.add(rs.getString(1));
				stmt1 = conn.createStatement();
				result = stmt1.executeUpdate("update polish set comment = '"+comments+"' where polishID='"+respond.get(2).toString()+"';");
				return respond;
			}
			else
			{
				respond.add(1,"unvalid");
				return respond;
			}
		}catch(SQLException e)
		{
			//e.printStackTrace();
			//System.out.println("ERROR - src->server->mySQL->updateCommentsIntoPolishRow()- Couldnt get polishID");
			respond.add(1,"unvalid");
			return respond;
		}

	}
/**
 *This method returns the the comment in polish table in order to append it at GUI - MyInfoPanel 
 * @param message1	0:updateCommentsFromPolish , 1:jarID , 2:orderID 
 * @return ArrayList message	0:updateCommentsFromPolish , 1: valid \ unvalid , 2:comments
 */

	public ArrayList<Object> updateGuiCommentsFromPolish(ArrayList<Object> message1) {
		
		Statement stmt = null,stmt1 = null;
		ResultSet rs;	
		int result;
		ArrayList<Object>message=message1 ;
		String jarID=message.get(1).toString();
		String orderID = message.get(2).toString();
		ArrayList<Object>respond = new ArrayList<>();
		respond.add("updateCommentsFromPolish");
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select polish.comment from polish , orders , ordersinpolish where orders.orderID='"+orderID+"' AND orders.orderID = ordersinpolish.orderID AND polish.polishID=ordersinpolish.polishID AND polish.jarID='"+jarID+"' AND orders.statusID='31';");
			while(rs.next())
			{
				respond.add(1,"valid");
				respond.add(rs.getString(1));
				return respond;
			}
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add(1,"unvalid");
			return respond;
		}
		
		
		respond.add(1,"unvalid");
		return respond;
	}

/**
 * This method adds a new package report to "packagereport" table .
 * @param message1	0:addNewPackageReport , 1:orderID , 2:date , 3:quantityToPack , 4:employeeName , 5:employeeName
 * @return	ArrayList message	0:addNewPackageReport , 1:valid \ unvalid
 */
	public ArrayList<Object> addNewPackageReport(ArrayList<Object> message1) {
		Statement stmt = null,stmt1 = null;
		ResultSet rs;	
		int result;
		ArrayList<Object>message=message1 ;
		String orderID=message.get(1).toString();
		String date = message.get(2).toString();
		String quantityToPack = message.get(3).toString();
		String employeeName = message.get(4).toString();
		String packageStep = message.get(5).toString();
		String pEmployeeID=null;
		
		// Translating formated date from :"dd/MM/yyyy" to :"yyyy/MM/dd".  
		final String OLD_FORMAT = "dd/MM/yyyy";
		final String NEW_FORMAT = "yyyy/MM/dd";
		String oldDateString = date;
		String newDateString=null;
		try{
			SimpleDateFormat sdf = new SimpleDateFormat(OLD_FORMAT);
			java.util.Date d = sdf.parse(oldDateString);
			sdf.applyPattern(NEW_FORMAT);
			newDateString = sdf.format(d);
			newDateString.toString();
		}catch(Exception e)
		{
			//e.printStackTrace();
		}
		// End translating formate of date .
				
		ArrayList<Object>respond = new ArrayList<>();
		respond.add("addNewPackageReport");
		try
		{
			stmt = conn.createStatement();
			stmt1= conn.createStatement();
			//Resolve  employee name from employeepackaging table.
			rs = stmt.executeQuery("select pEmployeeID from employeepackaging where pName='"+employeeName+"'");
			while(rs.next())
			{
				pEmployeeID = rs.getString(1).toString();
			}
			rs.close();
			//This is the main query , creats a new row if needed into packagereport , if not needed it will append to it .
			result=stmt1.executeUpdate("INSERT into packagereport (orderID , date , quantity , pEmployeeID , packageStep) values ('"+orderID+"','"+newDateString+"','"+quantityToPack+"','"+pEmployeeID+"','"+packageStep+"');");
			result+=stmt1.executeUpdate("update orders set statusID = '81' where orderID='"+orderID+"'");
			if(result==2)
			{
				//rs.close();
				stmt.close();
				respond.add("valid");
				return respond;
			}
			respond.add("unvalid");
			//rs.close();
			stmt.close();
			return respond;
		}catch(SQLException e)
		{
			//e.printStackTrace();
			//System.out.println("ERROR - src->server->mySQL->addNewPackageReport");
			respond.add(1,"unvalid");
			return respond;
		}
	}
	
	/**
	 * This method updates an existing package report .
	 * @param message1	0:addNewPackageReport , 1:orderID , 2:date , 3:quantityToPack , 4:employeeName , 5:employeeName , 6:packageID
	 * @return	ArrayList message	0:addNewPackageReport , 1:valid \ unvalid
	 */
	public ArrayList<Object> updateNewPackageReport(ArrayList<Object> message1) {
	
		Statement stmt = null,stmt1 = null;
		ResultSet rs;	
		int result;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		String orderID=message.get(1).toString();
		String date = message.get(2).toString();
		String quantityToPack = message.get(3).toString();
		String employeeName = message.get(4).toString();
		String packageStep = message.get(5).toString();
		String packageID = message.get(6).toString();
		String pEmployeeID=null;
		
		// Translating formated date from :"dd/MM/yyyy" to :"yyyy/MM/dd".  
				final String OLD_FORMAT = "dd/MM/yyyy";
				final String NEW_FORMAT = "yyyy/MM/dd";
				String oldDateString = date;
				String newDateString=null;
				try{
					SimpleDateFormat sdf = new SimpleDateFormat(OLD_FORMAT);
					java.util.Date d = sdf.parse(oldDateString);
					sdf.applyPattern(NEW_FORMAT);
					newDateString = sdf.format(d);
					newDateString.toString();
				}catch(Exception e)
				{
					//e.printStackTrace();
				}
				// End translating formate of date .
		
		
		String quantity; // will hold the quantity that is already packed and will be added to it the new quantity
		respond.add("updateNewPackageReport");
		// somthing to be continued.... need to take under consediration that the user didnt fill in all fields 
		try{
			stmt = conn.createStatement();
			//return the employee id from employeepackaging
			rs = stmt.executeQuery("select pEmployeeID from employeepackaging where pName = '"+employeeName+"';");
			if(rs.next())
			{
				pEmployeeID = rs.getString(1).toString();
			}
			rs = stmt.executeQuery("select packagereport.quantity from orders , packagereport where orders.statusID='81' and orders.orderID='"+orderID+"' AND packagereport.orderID = orders.orderID AND packagereport.packageID='"+packageID+"';");
			while(rs.next())
			{
				quantity = rs.getString(1).toString();
				int quantityINT = Integer.valueOf(quantity);
				int temp = Integer.valueOf(quantityToPack);
				quantityINT = quantityINT + temp;
				quantityToPack = String.valueOf(quantityINT);
			}
			//result = stmt.executeUpdate("update packagereport set (orderID , date , quantity , pEmployeeID , packageStep) values ('"+orderID+"','"+newDateString+"','"+quantityToPack+"','"+pEmployeeID+"','"+packageStep+"') where packageID = '"+packageID+"';");
			result = stmt.executeUpdate("update packagereport set orderID='"+orderID+"',date='"+newDateString+"',quantity='"+quantityToPack+"',pEmployeeID='"+pEmployeeID+"',packageStep='"+packageStep+"' where packageID = '"+packageID+"';");
			if(result == 1)
			{
				respond.add(1,"valid");
				stmt.close();
				return respond;
			}
			else{
				respond.add(1,"unvalid");
				return respond;
			}
		}catch(SQLException e)
		{
			//e.printStackTrace();
		}
		
		//end
		
		return null;
	}
	
	/**
	 *Searches for an specific packageID with orderId given (also checks status 81 - polishing phase). 
	 * @param message1 0:checkExistingRowPackagingReport , 1:orderID , 2:employeeName , 3:side 
	 * @return	ArrayList respond	0:checkExistingRowPackagingReport , 1:valid \ unvalid 
	 */
	public ArrayList<Object> checkExistingRowPackagingReport(ArrayList<Object> message1) {
		
		Statement stmt = null,stmt1 = null;
		ResultSet rs;	
		int result;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		String orderID=message.get(1).toString();
		String employeeName = message.get(2).toString();
		String side = message.get(3).toString();
		String packageID,pEmployeeID=null;
		respond.add("checkExistingRowPackagingReport");

		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select pEmployeeID from employeepackaging where pName = '"+employeeName+"';");
			if(rs.next())
			{
				pEmployeeID = rs.getString(1).toString();
			}
			//return packageID
			rs = stmt.executeQuery("select packagereport.packageID from orders , packagereport where orders.statusID='81' and orders.orderID='"+orderID+"' AND packagereport.orderID = orders.orderID AND packagereport.pEmployeeID='"+pEmployeeID+"' AND packagereport.packageStep='"+side+"';");
			while(rs.next())
			{
				packageID = rs.getString(1);
				respond.add(1,"valid");
				respond.add(packageID);
				rs.close();
				stmt.close();
				return respond;
			}
			rs.close();
			stmt.close();
			respond.add(1,"unvalid");
			return respond;
		}catch(SQLException e)
		{
			//e.printStackTrace();
			//System.out.println("ERROR - src->server->mySQL-> checkExistingRowPackagingReport()");
			respond.add(1,"unvalid");
			return respond;
		}
	}
	/**
	 *This method return the packaging employees names in order to display it in combo boxes at JTappedPanePackagingMain GUI 
	 * @param message1	0:getPackageEmployeesNames 
	 * @return	ArrayList respond	0:getPackageEmployeesNames , 1:valid \ unvalid , 2,3,4...:pName
	 */
	
	public ArrayList<Object> getPackageEmployeesNames(ArrayList<Object> message1) {
		
		Statement stmt = null;
		ResultSet rs1;	
		int result;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		respond.add("getPackageEmployeesNames");
		try{
			stmt = conn.createStatement();
			rs1 = stmt.executeQuery("select pName from employeepackaging ;");

			while(rs1.next())
			{
				respond.add(rs1.getString(1));
			}
			if(respond.size()==1)
				respond.add(1,"unvalid");
			else
			respond.add(1,"valid");
			return respond;
		}catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add(1,"unvalid");
			//System.out.println("ERROR - src->server->mySQL->getPackageEmployeesNames()");
			return respond;
		}
	}

//
	/**
	 * Returns quantity of orders .
	 * @param message1	0:getOrderIDQuantity , 1:orderID , 2:step 
	 * @return	ArrayList respond 	0:getOrderIDQuantity , 1:valud \ unvalid
	 */
	public ArrayList<Object> getOrderIDQuantity(ArrayList<Object> message1) {
		
		Statement stmt = null,stmt1=null,stmt2=null,stmt3=null,stmt4=null;
		ResultSet rs,rs1,rs2,rs3,rs4;	
		int result;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		String orderID = message.get(1).toString();
		String step = message.get(2).toString();
		String total,hashmashaQ,discardQ,packedQ,failSetQ;
		respond.add("getOrderIDQuantity");
		try{
			stmt = conn.createStatement();
			stmt1 = conn.createStatement();
			stmt2 = conn.createStatement();
			stmt3 = conn.createStatement();
			stmt4 = conn.createStatement();
			rs = stmt.executeQuery("select quantity from orders where orderID='"+message.get(1).toString()+"'");
			if(rs.next())
			{
				total = rs.getString(1);
				//returns quantity of new order\hashmasha , saves into :hashmashaQ .
				rs1 = stmt1.executeQuery("select orders.quantity from packagehashmasha  , orders where orders.orderID=packagehashmasha.hashmashaOrderID AND orders.statusID='7' and packagehashmasha.originalOrderID = '"+orderID+"';");
				while(rs1.next())
				{
					hashmashaQ = rs1.getString(1).toString();
					int totalINT = Integer.valueOf(total);
					int hashmashaINT = Integer.valueOf(hashmashaQ);
					totalINT = totalINT - hashmashaINT;
					total = String.valueOf(totalINT);
				}
				//getting the qunatity from packagediscard -> total = total - discarded quantity
				rs2 = stmt2.executeQuery("select quantity from packagediscard where orderID = '"+orderID+"'");
				while(rs2.next())
				{
					int totalINT = Integer.valueOf(total);
					int discardINT = Integer.valueOf(rs2.getString(1).toString());
					totalINT = totalINT - discardINT;
					total = String.valueOf(totalINT);
				}
				//returning the packed quantity -> total = total - quantity packed
				rs3 = stmt3.executeQuery("select packagereport.quantity from packagereport ,orders where orders.orderID = packagereport.orderID AND packagereport.orderID='"+orderID+"' AND orders.statusID='81' AND packagereport.packageStep='"+step+"'");
				while(rs3.next())
				{
					int totalINT = Integer.valueOf(total);
					int packedINT = Integer.valueOf(rs3.getString(1).toString());
					totalINT = totalINT - packedINT;
					total = String.valueOf(totalINT);
				}
				//returning the failset quantity from quality control fromt he same orderID -> total - total - failsetuqantity
				rs4 = stmt4.executeQuery("select failsetflag from qualitycontrol where orderID = '"+orderID+"'");
				while(rs4.next())
				{
					int totalINT = Integer.valueOf(total);
					int failsetINT = Integer.valueOf(rs4.getString(1).toString());
					totalINT = totalINT - failsetINT;
					total = String.valueOf(totalINT);
				}
				respond.add(1,"valid");
				respond.add(total);
				return respond;
				
			}
			else{
				respond.add(1,"unvalid");
				return respond;
			}
			
		}catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add(1,"unvalid");
			return respond;
		}

	}



	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getFailQuantityReport(RequestParameters requestParameters)
	{
	

		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					//System.out.println("SELECT qualitycontrol.orderID, products.productFamily, orders.type, orders.diopter, SUM(qualitycontrol.failSetFlag), orders.quantity FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY orders.type,orders.diopter ORDER BY orders.diopter;");
					rs = stmt.executeQuery("SELECT qualitycontrol.orderID, products.productFamily, orders.type, orders.diopter, SUM(qualitycontrol.failSetFlag), orders.quantity FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY orders.type,orders.diopter ORDER BY orders.diopter;");
				}
				else
				{
					//System.out.println("SELECT qualitycontrol.orderID, products.productFamily, orders.type, orders.diopter, SUM(qualitycontrol.failSetFlag), orders.quantity FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY orders.type,orders.diopter ORDER BY orders.diopter;");
					rs = stmt.executeQuery("SELECT qualitycontrol.orderID, products.productFamily, orders.type, orders.diopter, SUM(qualitycontrol.failSetFlag), orders.quantity FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY orders.type,orders.diopter ORDER BY orders.diopter;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
					order.setOrderID(rs.getInt(1));
					order.setProductFamily(rs.getString(2));
					order.setType(rs.getString(3));
					order.setDiopter(rs.getFloat(4));
					order.setFails(rs.getString(5));
					order.setQuantity(rs.getInt(6));
					
					response.add(order);
					counter++;
					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
				
				//e.printStackTrace();
			}
		

		return response;
	}

	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getFailPercentagesReport(RequestParameters requestParameters)
	{

		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					//System.out.println("SELECT qualitycontrol.failSetFlag/orders.quantity, products.productFamily, orders.type, orders.diopter FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY orders.diopter,orders.type ORDER BY orders.diopter;");
					rs = stmt.executeQuery("SELECT qualitycontrol.failSetFlag/orders.quantity, products.productFamily, orders.type, orders.diopter FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY orders.diopter,orders.type ORDER BY orders.diopter;");
				}
				else
				{
					//System.out.println("SELECT qualitycontrol.failSetFlag/orders.quantity, products.productFamily, orders.type, orders.diopter FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY orders.diopter,orders.type ORDER BY orders.diopter;");
					rs = stmt.executeQuery("SELECT qualitycontrol.failSetFlag/orders.quantity, products.productFamily, orders.type, orders.diopter FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY orders.diopter,orders.type ORDER BY orders.diopter;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
					order.setFailsPercentage(rs.getFloat(1));
					order.setProductFamily(rs.getString(2));
					order.setType(rs.getString(3));
					order.setDiopter(rs.getFloat(4));
										
					response.add(order);
					counter++;
					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
			
				//e.printStackTrace();
			}
		

		return response;
	}
/**
 * This method inserts into "packagediscard" a new record 
 * @param message1	0:discardPackage , 1:orderID , 2:quantity , 3:comments
 * @return	ArrayList respond	0:discardPackage , 1:valid \ unvalid
 */
	public ArrayList<Object> discardPackage(ArrayList<Object> message1) {
		
		Statement stmt = null;
		ResultSet rs1;	
		int result;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		String orderID = message.get(1).toString();
		String quantity = message.get(2).toString();
		String comments = message.get(3).toString();
		Calendar cal = Calendar.getInstance();
		Date today = new Date(cal.getTimeInMillis());
		String todayStr = today.toString();
		respond.add("discardPackage");
		try{
			stmt = conn.createStatement();
			
			result = stmt.executeUpdate("insert  IGNORE INTO packagediscard (orderID , quantity , discardComments, discardDate) values ('"+orderID+"','"+quantity+"','"+comments+"','"+todayStr+"');");
			if(result == 1)
			{
				respond.add(1,"valid");
				return respond;
			}
			else{
				respond.add(1,"unvalid");
				return respond;
			}

		}catch(SQLException e)
		{
			//e.printStackTrace();
			respond.add(1,"unvalid");
			//System.out.println("ERROR - src->server->mySQL->discardPackage()");
			return respond;
		}

	}
/**
 *This method checks if there is such orderID in database , if not , it creates a new order after Hashmasha 
 * @param message1	0:createNewOrderhashmasha , 1:orderID , 2:type , 3:diopter , 4:date , 5:quantity , 6:status
 * @return	ArrayList respond	0:createNewOrderhashmasha , 1:valid \ unvalid
 */
//
	public ArrayList<Object> createNewOrderhashmasha(ArrayList<Object> message1) {
		
		Statement stmt = null,stmtCheck=null;
		ResultSet rs1,rsCheck;	
		int result;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		String orderID = message.get(1).toString();
		String type = message.get(2).toString();
		String diopter = message.get(3).toString();
		String date = message.get(4).toString();
		String quantity = message.get(5).toString();
		String status = message.get(6).toString();
		respond.add("createNewOrderhashmasha");
		try
		{
			stmtCheck = conn.createStatement();
			rsCheck = stmtCheck.executeQuery("select orderID from orders where orderID = '"+ orderID +"'; ");
			if(rsCheck.next())
			{
				respond.add(1,"unvalid");
				return respond;
			}
			stmt = conn.createStatement();
			result = stmt.executeUpdate("INSERT IGNORE INTO orders(orderID,type,diopter,orderDate,quantity,statusID) VALUES('" + orderID + "','" +type+ "','" + diopter + "','" + date + "','" + quantity +  "','" + status + "');");
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			//System.out.println("ERROR - src->server->mySQL-> createNewOrderhashmasha()");
			respond.add(1,"unvalid");
			return respond;
		}
		if(result == 1)
		{
			respond.add(1,"valid");
			return respond;
		}
		respond.add(1,"unvalid");
		return respond;
		
	}
/**
 *Insert new record to "Hashmasha" table -- who uses it ? - src-JTappedPanelPackagingMain -Hashmasha 
 * @param message1	0:createNewPackageHashmasha , 1:originalOrderID , 2:hashmashaOrderID , 3:comments
 * @return	ArrayList respond	0:createNewPackageHashmasha , 1:valid \ unvalid 
 */

	public ArrayList<Object> createNewPackageHashmasha(ArrayList<Object> message1) {
		
		Statement stmt = null;
		ResultSet rs1;	
		int result=0;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		String originalOrderID = message.get(1).toString();
		String hashmashaOrderID = message.get(2).toString();
		String comments = message.get(3).toString();
		
		respond.add("createNewPackageHashmasha");
		try{
			stmt = conn.createStatement();
			result = stmt.executeUpdate("INSERT IGNORE INTO packagehashmasha (originalOrderID , hashmashaOrderID , comments) values ('"+ originalOrderID +"' , '"+ hashmashaOrderID +"' , '"+ comments +"' );");
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			//System.out.println("ERROR - src->server->mySQL->createNewPackageHashmasha() -> Couldn't insert a new row to packagehashmasha");
			respond.add(1,"unvalid");
			return respond;
		}
		if(result == 1)
		{
			respond.add(1,"valid");
			return respond;
		}
		else{
			respond.add(1,"unvalid");
			return respond;
		}
		
	}
/**
 *This method finishes the packaging phase , sets the statusID of a given orededID to status 99 (complete) 
 * @param message1	0:finishPackaging , 1:orderID
 * @return	ArrayList respond	0:finishPackaging , 1:valid \ unvalid
 */
	public ArrayList<Object> finishPackaging(ArrayList<Object> message1) {
		
		Statement stmt = null;
		ResultSet rs1;	
		int result=0;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
		String orderID = message.get(1).toString();
		respond.add("finishPackaging");
		try
		{
			stmt = conn.createStatement();
			result = stmt.executeUpdate("UPDATE orders set statusID='99' where orderID = '"+ orderID +"'");
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			//System.out.println("ERROR - src->server->mySQL->finishPackaging()");
			respond.add(1,"unvalid");
			return respond;
		}
		if(result == 1)
		{
			respond.add(1,"valid");
			return respond;
		}
		else
		{
			respond.add(1,"unvalid");
			return respond;
		}
		

	}

	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getFailQuantitiesByEngraveMonth(RequestParameters requestParameters)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT orders.orderID, DATE_FORMAT(engrave.engraveDate,'%m/20%y')as DateAsMonthYear, products.productFamily, SUM(qualitycontrol.failSetFlag), orders.quantity FROM engrave,qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND engrave.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND engrave.engraveDate >= '" + requestParameters.getStartTimestamp() + "' AND engrave.engraveDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY DateAsMonthYear,products.productFamily ORDER BY engrave.engraveDate ASC;");
				}
				else
				{

					rs = stmt.executeQuery("SELECT orders.orderID, DATE_FORMAT(engrave.engraveDate,'%m/20%y')as DateAsMonthYear, products.productFamily, SUM(qualitycontrol.failSetFlag), orders.quantity FROM engrave,qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND engrave.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND engrave.engraveDate >= '" + requestParameters.getStartTimestamp() + "' AND engrave.engraveDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY DateAsMonthYear,products.productFamily ORDER BY engrave.engraveDate ASC;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
					order.setOrderID(rs.getInt(1));
				
					order.setDateAsMonthYear(rs.getString(2));
					order.setProductFamily(rs.getString(3));
					order.setFails(rs.getString(4));
					order.setQuantity(rs.getInt(5));
					response.add(order);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
			
				//e.printStackTrace();
			}
		

		return response;
	}


	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getFailQuantitiesByQCMonth(RequestParameters requestParameters)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					// SELECT orders.orderID, DATE_FORMAT(engrave.engraveDate,'%m/20%y'), products.productFamily, qualitycontrol.failSetFlag FROM engrave,qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND engrave.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "';
					//System.out.println("SELECT orders.orderID, DATE_FORMAT(qualitycontrol.qcDate,'%m/20%y') as DateAsMonthYear, products.productFamily, SUM(qualitycontrol.failSetFlag), orders.quantity FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY DateAsMonthYear,products.productFamily ORDER BY qualitycontrol.qcDate ASC;");
					rs = stmt.executeQuery("SELECT orders.orderID, DATE_FORMAT(qualitycontrol.qcDate,'%m/20%y') as DateAsMonthYear, products.productFamily, SUM(qualitycontrol.failSetFlag), orders.quantity FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY DateAsMonthYear,products.productFamily ORDER BY qualitycontrol.qcDate ASC;");
				}
				else
				{
					// "SELECT orders.orderID, DATE_FORMAT(engrave.engraveDate,'%m/20%y'), products.productFamily, qualitycontrol.failSetFlag FROM engrave,qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND engrave.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "';"
					//System.out.println("SELECT orders.orderID, DATE_FORMAT(qualitycontrol.qcDate,'%m/20%y') as DateAsMonthYear, products.productFamily, SUM(qualitycontrol.failSetFlag), orders.quantity FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY DateAsMonthYear,products.productFamily ORDER BY qualitycontrol.qcDate ASC;");
					rs = stmt.executeQuery("SELECT orders.orderID, DATE_FORMAT(qualitycontrol.qcDate,'%m/20%y') as DateAsMonthYear, products.productFamily, SUM(qualitycontrol.failSetFlag), orders.quantity FROM qualitycontrol,orders,products WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY DateAsMonthYear,products.productFamily ORDER BY qualitycontrol.qcDate ASC;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
					order.setOrderID(rs.getInt(1));
					//order.setQcDate(rs.getTimestamp(2));
					order.setDateAsMonthYear(rs.getString(2));
					order.setProductFamily(rs.getString(3));
					order.setFails(rs.getString(4));
					order.setQuantity(rs.getInt(5));
					response.add(order);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
			
				//e.printStackTrace();
			}
		

		return response;
	}


	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getFailQuantitiesByReason(RequestParameters requestParameters)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		response.add (requestParameters.getFamily());
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT products.productFamily, SUM(failsets.scratches), SUM(failsets.stuckChip), SUM(failsets.engraveMarks), SUM(failsets.aoptic), SUM(failsets.diopterDeviation), SUM(failsets.surface), SUM(failsets.dirt), SUM(failsets.dropedOrLost), SUM(failsets.bumps), SUM(failsets.hydration), SUM(failsets.loops), SUM(failsets.fractures), SUM(failsets.milling), SUM(failsets.asymmetry), SUM(failsets.characteristics), SUM(failsets.orange), SUM(failsets.c1TurnLens), SUM(failsets.c2ChippedEdge), SUM(failsets.c3CuttingChips), SUM(failsets.c4Scratches), SUM(failsets.c5Prism), SUM(failsets.c6FoldedLens), SUM(failsets.c7OODDiameter), SUM(failsets.c8OODThickness), SUM(failsets.c9Lost), SUM(failsets.c10LatheMarks), SUM(failsets.c11ChippedHole), SUM(failsets.c12NoHole), SUM(failsets.c13UndefinedParticles), SUM(failsets.c14FoggyResolution), SUM(failsets.c15OODDiopterPower), SUM(failsets.c16OODPlanoPower), SUM(failsets.engravingStationEngraving), SUM(failsets.engravingStationCharacteristics), SUM(failsets.engravingStationDroppedOrLost),SUM(failsets.dryFails) FROM orders,failsets,qualitycontrol,products WHERE orders.orderID = failsets.orderID AND orders.orderID = qualitycontrol.orderID AND orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY products.productFamily;");
					
					while (rs.next())
					{
						FailSet set = new FailSet();
						
						set.setProductFamily(rs.getString(1));
						
						set.setScratches(rs.getString(2));
						set.setStuckChip(rs.getString(3));
						set.setEngraveMarks(rs.getString(4));
						set.setAoptic(rs.getString(5));
						set.setDiopterDeviation(rs.getString(6));
						set.setSurface(rs.getString(7));
						set.setDirt(rs.getString(8));
						set.setDropedOrLost(rs.getString(9));
						set.setBumps(rs.getString(10));
						set.setHydration(rs.getString(11));
						set.setLoops(rs.getString(12));
						set.setFractures(rs.getString(13));
						set.setMilling(rs.getString(14));
						set.setAsymmetry(rs.getString(15));
						set.setCharacteristics(rs.getString(16));
						set.setOrange(rs.getString(17));
						
						set.setC1TurnLens(rs.getString(18));
						set.setC2ChippedEdge(rs.getString(19));
						set.setC3CuttingChips(rs.getString(20));
						set.setC4Scratches(rs.getString(21));
						set.setC5Prism(rs.getString(22));
						set.setC6FoldedLens(rs.getString(23));
						set.setC7OODDiameter(rs.getString(24));
						set.setC8OODThickness(rs.getString(25));
						set.setC9Lost(rs.getString(26));
						set.setC10LatheMarks(rs.getString(27));
						set.setC11ChippedHole(rs.getString(28));
						set.setC12NoHole(rs.getString(29));
						set.setC13UndefinedParticles(rs.getString(30));
						set.setC14FoggyResolution(rs.getString(31));
						set.setC15OODDiopterPower(rs.getString(32));
						set.setC16OODPlanoPower(rs.getString(33));
						set.setEngravingStationEngraving(rs.getString(34));
						set.setEngravingStationCharacteristics(rs.getString(35));
						set.setEngravingStationDroppedOrLost(rs.getString(36));
						set.setDryFails(rs.getString(37));
						
						
						response.add(set);
						counter++;					
					}
				}
				else
				{
					if (requestParameters.getFamily().equalsIgnoreCase("פרסביה")) // Just Presbia 
					{
					
						rs = stmt.executeQuery("SELECT products.productFamily, SUM(failsets.c1TurnLens), SUM(failsets.c2ChippedEdge), SUM(failsets.c3CuttingChips), SUM(failsets.c4Scratches), SUM(failsets.c5Prism), SUM(failsets.c6FoldedLens), SUM(failsets.c7OODDiameter), SUM(failsets.c8OODThickness), SUM(failsets.c9Lost), SUM(failsets.c10LatheMarks), SUM(failsets.c11ChippedHole), SUM(failsets.c12NoHole), SUM(failsets.c13UndefinedParticles), SUM(failsets.c14FoggyResolution), SUM(failsets.c15OODDiopterPower), SUM(failsets.c16OODPlanoPower), SUM(failsets.dryFails) FROM orders,failsets,qualitycontrol,products WHERE orders.orderID = failsets.orderID AND orders.orderID = qualitycontrol.orderID AND orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "';");
						
						while (rs.next())
						{
							FailSet set = new FailSet();
							
							set.setProductFamily(rs.getString(1));
							
							set.setC1TurnLens(rs.getString(2));
							set.setC2ChippedEdge(rs.getString(3));
							set.setC3CuttingChips(rs.getString(4));
							set.setC4Scratches(rs.getString(5));
							set.setC5Prism(rs.getString(6));
							set.setC6FoldedLens(rs.getString(7));
							set.setC7OODDiameter(rs.getString(8));
							set.setC8OODThickness(rs.getString(9));
							set.setC9Lost(rs.getString(10));
							set.setC10LatheMarks(rs.getString(11));
							set.setC11ChippedHole(rs.getString(12));
							set.setC12NoHole(rs.getString(13));
							set.setC13UndefinedParticles(rs.getString(14));
							set.setC14FoggyResolution(rs.getString(15));
							set.setC15OODDiopterPower(rs.getString(16));
							set.setC16OODPlanoPower(rs.getString(17));
							set.setDryFails(rs.getString(18));
							
							response.add(set);
							counter++;				
							
						}
					}
					else // Anything but Presbia							
					{
					
							rs = stmt.executeQuery("SELECT products.productFamily, SUM(failsets.scratches), SUM(failsets.stuckChip), SUM(failsets.engraveMarks), SUM(failsets.aoptic), SUM(failsets.diopterDeviation), SUM(failsets.surface), SUM(failsets.dirt), SUM(failsets.dropedOrLost), SUM(failsets.bumps), SUM(failsets.hydration), SUM(failsets.loops), SUM(failsets.fractures), SUM(failsets.milling), SUM(failsets.asymmetry), SUM(failsets.characteristics), SUM(failsets.orange), SUM(failsets.engravingStationEngraving), SUM(failsets.engravingStationCharacteristics), SUM(failsets.engravingStationDroppedOrLost) FROM orders,failsets,qualitycontrol,products WHERE orders.orderID = failsets.orderID AND orders.orderID = qualitycontrol.orderID AND orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "';");
							
							while (rs.next())
							{
								FailSet set = new FailSet();
								
								set.setProductFamily(rs.getString(1));
								
								set.setScratches(rs.getString(2));
								set.setStuckChip(rs.getString(3));
								set.setEngraveMarks(rs.getString(4));
								set.setAoptic(rs.getString(5));
								set.setDiopterDeviation(rs.getString(6));
								set.setSurface(rs.getString(7));
								set.setDirt(rs.getString(8));
								set.setDropedOrLost(rs.getString(9));
								set.setBumps(rs.getString(10));
								set.setHydration(rs.getString(11));
								set.setLoops(rs.getString(12));
								set.setFractures(rs.getString(13));
								set.setMilling(rs.getString(14));
								set.setAsymmetry(rs.getString(15));
								set.setCharacteristics(rs.getString(16));
								set.setOrange(rs.getString(17));
								
								set.setEngravingStationEngraving(rs.getString(18));
								set.setEngravingStationCharacteristics(rs.getString(19));
								set.setEngravingStationDroppedOrLost(rs.getString(20));
								
								response.add(set);
								counter++;				
								
							}								
						}
				}
						
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
			
				//e.printStackTrace();
			}
		

		return response;
	}


	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getFailQuantitiesByLine(RequestParameters requestParameters)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT engrave.line, products.productFamily,orders.type, sum(qualitycontrol.failSetFlag) FROM engrave,qualitycontrol,orders,products WHERE qualitycontrol.orderID = engrave.orderID AND qualitycontrol.orderID = orders.orderID AND orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY engrave.line,orders.type;");
				}
				else
				{
					
					rs = stmt.executeQuery("SELECT engrave.line, products.productFamily,orders.type, sum(qualitycontrol.failSetFlag) FROM engrave,qualitycontrol,orders,products WHERE qualitycontrol.orderID = engrave.orderID AND qualitycontrol.orderID = orders.orderID AND orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY engrave.line,orders.type;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
					order.setLine(rs.getString(1));
					order.setProductFamily(rs.getString(2));
					order.setType(rs.getString(3));
					order.setFails(rs.getString(4));
					
					response.add(order);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
				
				//e.printStackTrace();
			}
		

		return response;
	}


	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getEngraveQuantityByEmployeeReport(RequestParameters requestParameters) 
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT employees.name, sum(engrave.sideAquantity) as sideA, sum(engrave.sideBquantity) as sideB FROM employees,engrave,orders WHERE employees.employeeID = engrave.employeeID AND orders.orderID = engrave.orderID AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND engrave.engraveDate >= '" + requestParameters.getStartTimestamp() + "' AND engrave.engraveDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY employees.employeeID ORDER BY sideB DESC;");
				}
				else
				{
					
					rs = stmt.executeQuery("SELECT employees.name, sum(engrave.sideAquantity) as sideA, sum(engrave.sideBquantity) as sideB FROM employees,engrave,orders,products WHERE employees.employeeID = engrave.employeeID AND orders.orderID = engrave.orderID AND orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND engrave.engraveDate >= '" + requestParameters.getStartTimestamp() + "' AND engrave.engraveDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY employees.employeeID ORDER BY sideB DESC;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
					order.setEmployee(rs.getString(1));
					order.setSideA(rs.getInt(2));
					order.setSideB(rs.getInt(3));
					
					
					response.add(order);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
			
				//e.printStackTrace();
			}
		

		return response;
	}
/**
 *This method is responsible on initializing the POLISH REPORT on the VPO dashboard 
 * @param message1	0:initializeVPOfOperationsTabbedPolishReport , 1:beginDate , 2:endDate , 3:startDiopter , 4:endDiopter , 5:family
 * @return	ArrayList respond	0:initializeVPOfOperationsTabbedPolishReport , 1:valid \ unvalid , 2,3,4...:PolishReportEntity
 */

	public ArrayList<Object> initializeVPOfOperationsTabbedPolishReport(ArrayList<Object> message1) {
		
		Statement stmt = null,stmt1=null;
		ResultSet rs,rs1;	
		int result=0;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
			
		String beginDate = message.get(1).toString();
		String endDate = message.get(2).toString();
	
		//System.out.print(beginDate.toString());
		String startDiopter = message.get(3).toString();
		String endDiopter = message.get(4).toString();
		String family = message.get(5).toString();
		
		// Translating formated date from :"dd/MM/yyyy" to :"yyyy/MM/dd".  
				final String OLD_FORMAT = "dd/MM/yyyy";
				final String NEW_FORMAT = "yyyy/MM/dd";
				String oldDateStringBegin = beginDate;
				String oldDateStringEnd = endDate;
				String newDateString=null;
				String newDateStringg=null;
				try{
					SimpleDateFormat sdf = new SimpleDateFormat(OLD_FORMAT);
					SimpleDateFormat sdff= new SimpleDateFormat(OLD_FORMAT);
					java.util.Date d = sdf.parse(oldDateStringBegin);
					java.util.Date dd = sdff.parse(oldDateStringEnd);
					sdf.applyPattern(NEW_FORMAT);
					sdff.applyPattern(NEW_FORMAT);
					newDateString = sdf.format(d);
					newDateStringg = sdff.format(dd);
					newDateString.toString();
					newDateStringg.toString();
				}catch(Exception e)
				{
					//e.printStackTrace();
				}
				// End translating formate of date .
		
		respond.add("initializeVPOfOperationsTabbedPolishReport");
		if(family.equalsIgnoreCase("הכל"))
		{	
			try
			{
				stmt = conn.createStatement();
				rs = stmt.executeQuery("select orders.orderID , orders.type , orders.diopter , orders.quantity, polish.polishID ,polish.jarID , polish.beginDate , polish.endDate ,polish.topline from orders , ordersinpolish , polish where orders.orderID = ordersinpolish.orderID AND polish.polishID = ordersinpolish.polishID AND polish.beginDate > '"+newDateString+"' AND polish.endDate < '"+newDateStringg+"' AND orders.diopter > '"+startDiopter+"' AND orders.diopter < '"+endDiopter+"' ;");
				while(rs.next())
				{
					PolishReportEntity myEntity = new PolishReportEntity();
					myEntity.setOrderID(rs.getString(1).toString());
					myEntity.setType(rs.getString(2).toString());
					myEntity.setDiopter(rs.getString(3).toString());
					myEntity.setQuantity(rs.getString(4).toString());
					myEntity.setPolishID(rs.getString(5).toString());
					myEntity.setJarID(rs.getString(6).toString());
					myEntity.setBeginDate(rs.getString(7).toString());
					myEntity.setEndDate(rs.getString(8).toString());
					myEntity.setTopline(rs.getString(9).toString());
					respond.add(myEntity);
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}
			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				//System.out.println("ERROR - server->mySQL-> initializeVPOfOperationsTabbedPolishReport()");
				respond.add(1,"unvalid");
				return respond;
			}
		}
		else
		{
			try
			{
				stmt = conn.createStatement();
				stmt1 = conn.createStatement();
				rs1 = stmt1.executeQuery("select productName from products where products.productFamily='"+family+"'");
				while (rs1.next())
				{
					rs = stmt.executeQuery("select orders.orderID , orders.type , orders.diopter , orders.quantity, polish.polishID ,polish.jarID , polish.beginDate , polish.endDate ,polish.topline from orders , ordersinpolish , polish where orders.orderID = ordersinpolish.orderID AND polish.polishID = ordersinpolish.polishID AND polish.beginDate > '"+newDateString+"' AND polish.endDate < '"+newDateStringg+"' AND orders.diopter > '"+startDiopter+"' AND orders.diopter < '"+endDiopter+"' AND orders.type = '"+rs1.getString(1).toString()+"';");
					while(rs.next())
					{
						PolishReportEntity myEntity = new PolishReportEntity();
						myEntity.setOrderID(rs.getString(1).toString());
						myEntity.setType(rs.getString(2).toString());
						myEntity.setDiopter(rs.getString(3).toString());
						myEntity.setQuantity(rs.getString(4).toString());
						myEntity.setPolishID(rs.getString(5).toString());
						myEntity.setJarID(rs.getString(6).toString());
						myEntity.setBeginDate(rs.getString(7).toString());
						myEntity.setEndDate(rs.getString(8).toString());
						myEntity.setTopline(rs.getString(9).toString());
						respond.add(myEntity);
					}
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}

			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				respond.add(1,"unvalid");
				return respond;
			}

		}
	}


	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> VPOfOperationsFailQuantitiesByEmployeeReport(RequestParameters requestParameters) 
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT employees.name, products.productName, SUM(qualitycontrol.failSetFlag) as fails FROM products,orders,engrave,employees,qualitycontrol WHERE employees.employeeID = engrave.employeeID AND orders.orderID = engrave.orderID AND qualitycontrol.orderID = orders.orderID AND orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND engrave.engraveDate >= '" + requestParameters.getStartTimestamp() + "' AND engrave.engraveDate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY employees.name,products.productFamily ORDER BY fails DESC;");
				}
				else
				{
				
					rs = stmt.executeQuery("SELECT employees.name, products.productName, SUM(qualitycontrol.failSetFlag) as fails FROM products,orders,engrave,employees,qualitycontrol WHERE employees.employeeID = engrave.employeeID AND orders.orderID = engrave.orderID AND qualitycontrol.orderID = orders.orderID AND orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND engrave.engraveDate >= '" + requestParameters.getStartTimestamp() + "' AND engrave.engraveDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY employees.name,products.productFamily ORDER BY fails DESC;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
					order.setEmployee(rs.getString(1));
					order.setProductFamily(rs.getString(2));
					order.setFails(rs.getString(3));
										
					
					response.add(order);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
				
				//e.printStackTrace();
			}
		

		return response;
	}


	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getFailPercentagesByTimeEngraveReport(RequestParameters requestParameters)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT SUM(qualitycontrol.failSetFlag)/SUM(orders.quantity), products.productFamily, DATE_FORMAT(engrave.engraveDate,'%m/%Y') as engraveMonth FROM qualitycontrol,orders,products,engrave WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.orderID = engrave.orderID AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND orders.quantity > 0  GROUP BY engraveMonth,products.productFamily ORDER BY engrave.engraveDate,products.productFamily ASC;");
				}
				else
				{
					
				 	rs = stmt.executeQuery("SELECT SUM(qualitycontrol.failSetFlag)/SUM(orders.quantity), products.productFamily, DATE_FORMAT(engrave.engraveDate,'%m/%Y') as engraveMonth FROM qualitycontrol,orders,products,engrave WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.orderID = engrave.orderID AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' AND orders.quantity > 0  GROUP BY engraveMonth,products.productFamily ORDER BY engrave.engraveDate, products.productFamily ASC;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
		
					order.setFailsPercentage(rs.getFloat(1));
					order.setProductFamily(rs.getString(2));
					order.setDateAsMonthYear(rs.getString(3));
										
					response.add(order);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
			
				//e.printStackTrace();
			}
		

		return response;
	}
	

	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getFailPercentagesByTimeQCReport(RequestParameters requestParameters)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT SUM(qualitycontrol.failSetFlag)/SUM(orders.quantity), products.productFamily, DATE_FORMAT(qualitycontrol.qcDate,'%m/%Y') as engraveMonth FROM qualitycontrol,orders,products,engrave WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.orderID = engrave.orderID AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND orders.quantity > 0  GROUP BY engraveMonth,products.productFamily ORDER BY engrave.engraveDate,products.productFamily ASC;");
				}
				else
				{
					
					rs = stmt.executeQuery("SELECT SUM(qualitycontrol.failSetFlag)/SUM(orders.quantity), products.productFamily, DATE_FORMAT(qualitycontrol.qcDate,'%m/%Y') as engraveMonth FROM qualitycontrol,orders,products,engrave WHERE qualitycontrol.orderID = orders.orderID AND orders.type=products.productName AND orders.orderID = engrave.orderID AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND qualitycontrol.qcDate >= '" + requestParameters.getStartTimestamp() + "' AND qualitycontrol.qcDate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' AND orders.quantity > 0  GROUP BY engraveMonth,products.productFamily ORDER BY engrave.engraveDate, products.productFamily ASC;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
		
					order.setFailsPercentage(rs.getFloat(1));
					order.setProductFamily(rs.getString(2));
					order.setDateAsMonthYear(rs.getString(3));
										
					response.add(order);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
				
				//e.printStackTrace();
			}
		

		return response;
	}


	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getQuantitiesBySubFamilyReport(RequestParameters requestParameters)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT products.productFamily, products.productSubFamily, SUM(orders.quantity) FROM orders,products WHERE orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND orders.orderdate >= '" + requestParameters.getStartTimestamp() + "' AND orders.orderdate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY products.productSubFamily;");
				}
				else
				{
					
					rs = stmt.executeQuery("SELECT products.productFamily, products.productSubFamily, SUM(orders.quantity) FROM orders,products WHERE orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND orders.orderdate >= '" + requestParameters.getStartTimestamp() + "' AND orders.orderdate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY products.productSubFamily;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
		
					order.setProductFamily(rs.getString(1));
					order.setType(rs.getString(2)); // acts as sub family
					order.setQuantity(rs.getInt(3));
					
										
					response.add(order);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
				
				//e.printStackTrace();
			}
		

		return response;
	}
	
	

	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getQuantitiesByProductReport(RequestParameters requestParameters)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT products.productFamily, orders.type, SUM(orders.quantity) FROM orders,products WHERE orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND orders.orderdate >= '" + requestParameters.getStartTimestamp() + "' AND orders.orderdate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY orders.type ORDER BY productFamily;");
				}
				else
				{
					
					rs = stmt.executeQuery("SELECT products.productFamily, orders.type, SUM(orders.quantity) FROM orders,products WHERE orders.type = products.productName AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND orders.orderdate >= '" + requestParameters.getStartTimestamp() + "' AND orders.orderdate <= '" + requestParameters.getEndTimestamp() + "' AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY orders.type ORDER BY productFamily;");
				}
				
				while (rs.next())
				{
					ProductionOrder order = new ProductionOrder();
		
					order.setProductFamily(rs.getString(1));
					order.setType(rs.getString(2));
					order.setQuantity(rs.getInt(3));
					
										
					response.add(order);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
				
				//e.printStackTrace();
			}
		

		return response;
	}

/**
 * This method is responsible on initializing the Engrave REPORT on the VPO dashboard
 * @param message1	0:initializeVPOfOperationsTabbedEngraveReport , 1:beginDate , 2:endDate , 3:startDiopter , 4:endDiopter , 5:family
 * @return	ArrayList respond 0:initializeVPOfOperationsTabbedEngraveReport , 1:valid \ unvalid , 2,3,4...:EngraveReportEntity
 */
	public ArrayList<Object> initializeVPOfOperationsTabbedEngraveReport(ArrayList<Object> message1) {
		Statement stmt = null,stmt1=null;
		ResultSet rs,rs1;	
		int result=0;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
			
		String beginDate = message.get(1).toString();
		String endDate = message.get(2).toString();
	
		//System.out.print(beginDate.toString());
		String startDiopter = message.get(3).toString();
		String endDiopter = message.get(4).toString();
		String family = message.get(5).toString();
		
		// Translating formated date from :"dd/MM/yyyy" to :"yyyy/MM/dd".  
				final String OLD_FORMAT = "dd/MM/yyyy";
				final String NEW_FORMAT = "yyyy/MM/dd";
				String oldDateStringBegin = beginDate;
				String oldDateStringEnd = endDate;
				String newDateString=null;
				String newDateStringg=null;
				try{
					SimpleDateFormat sdf = new SimpleDateFormat(OLD_FORMAT);
					SimpleDateFormat sdff= new SimpleDateFormat(OLD_FORMAT);
					java.util.Date d = sdf.parse(oldDateStringBegin);
					java.util.Date dd = sdff.parse(oldDateStringEnd);
					sdf.applyPattern(NEW_FORMAT);
					sdff.applyPattern(NEW_FORMAT);
					newDateString = sdf.format(d);
					newDateStringg = sdff.format(dd);
					newDateString.toString();
					newDateStringg.toString();
				}catch(Exception e)
				{
					//e.printStackTrace();
				}
				// End translating formate of date .
		
		respond.add("initializeVPOfOperationsTabbedEngraveReport");
		if(family.equalsIgnoreCase("הכל"))
		{	
			try
			{
				stmt = conn.createStatement();
				rs = stmt.executeQuery("select orders.orderID , orders.type , orders.diopter , engrave.sideAquantity , engrave.sideBquantity , engrave.engraveDate , engrave.line from engrave,orders where orders.orderID=engrave.orderID AND engrave.engraveDate > '"+newDateString+"' AND engrave.engraveDate < '"+newDateStringg+"' AND orders.diopter > '"+startDiopter+"' AND orders.diopter < '"+endDiopter+"';");
				while(rs.next())
				{
					EngraveReportEntity myEntity = new EngraveReportEntity();
					myEntity.setOrderID(rs.getString(1).toString());
					myEntity.setType(rs.getString(2).toString());
					myEntity.setDiopter(rs.getString(3).toString());
					myEntity.setQuantityA(rs.getString(4).toString());
					myEntity.setQuantityB(rs.getString(5).toString());
					myEntity.setEngraveDate(rs.getString(6).toString());
					myEntity.setTopline(rs.getString(7).toString());;
					
					respond.add(myEntity);
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}
			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				//System.out.println("ERROR - server->mySQL-> initializeVPOfOperationsTabbedPolishReport()");
				respond.add(1,"unvalid");
				return respond;
			}
		}
		else
		{
			try
			{
				stmt = conn.createStatement();
				stmt1 = conn.createStatement();
				rs1 = stmt1.executeQuery("select productName from products where products.productFamily='"+family+"'");
				while (rs1.next())
				{
					rs = stmt.executeQuery("select orders.orderID , orders.type , orders.diopter , engrave.sideAquantity , engrave.sideBquantity , engrave.engraveDate , engrave.line from engrave,orders where orders.orderID=engrave.orderID AND engrave.engraveDate > '"+newDateString+"' AND engrave.engraveDate < '"+newDateStringg+"' AND orders.diopter > '"+startDiopter+"' AND orders.diopter < '"+endDiopter+"' AND orders.type = '"+rs1.getString(1).toString()+"';");
					while(rs.next())
					{
						EngraveReportEntity myEntity = new EngraveReportEntity();
						myEntity.setOrderID(rs.getString(1).toString());
						myEntity.setType(rs.getString(2).toString());
						myEntity.setDiopter(rs.getString(3).toString());
						myEntity.setQuantityA(rs.getString(4).toString());
						myEntity.setQuantityB(rs.getString(5).toString());
						myEntity.setEngraveDate(rs.getString(6).toString());
						myEntity.setTopline(rs.getString(7).toString());;
						
						respond.add(myEntity);
					}
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}

			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				respond.add(1,"unvalid");
				return respond;
			}

		}
	}

/**
 * This method is responsible on initializing the Quality control REPORT on the VPO dashboard
 * @param message1	0:initializeVPOfOperationsTabbedQCReport , 1:beginDate , 2:endDate , 3:startDiopter , 4:endDiopter , 5:family
 * @return	ArrayList respond 0:initializeVPOfOperationsTabbedQCReport , 1:valid \ unvalid , 2,3,4...:QCReportEntity
 */
	public ArrayList<Object> initializeVPOfOperationsTabbedQCReport(ArrayList<Object> message1) 
	{
		
		Statement stmt = null,stmt1=null;
		ResultSet rs,rs1;	
		int result=0;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
			
		String beginDate = message.get(1).toString();
		String endDate = message.get(2).toString();
	
		//System.out.print(beginDate.toString());
		String startDiopter = message.get(3).toString();
		String endDiopter = message.get(4).toString();
		String family = message.get(5).toString();
		
		// Translating formated date from :"dd/MM/yyyy" to :"yyyy/MM/dd".  
				final String OLD_FORMAT = "dd/MM/yyyy";
				final String NEW_FORMAT = "yyyy/MM/dd";
				String oldDateStringBegin = beginDate;
				String oldDateStringEnd = endDate;
				String newDateString=null;
				String newDateStringg=null;
				try{
					SimpleDateFormat sdf = new SimpleDateFormat(OLD_FORMAT);
					SimpleDateFormat sdff= new SimpleDateFormat(OLD_FORMAT);
					java.util.Date d = sdf.parse(oldDateStringBegin);
					java.util.Date dd = sdff.parse(oldDateStringEnd);
					sdf.applyPattern(NEW_FORMAT);
					sdff.applyPattern(NEW_FORMAT);
					newDateString = sdf.format(d);
					newDateStringg = sdff.format(dd);
					newDateString.toString();
					newDateStringg.toString();
				}catch(Exception e)
				{
					//e.printStackTrace();
				}
				// End translating formate of date .
		
		respond.add("initializeVPOfOperationsTabbedQCReport");
		if(family.equalsIgnoreCase("הכל"))
		{
			try
			{
				stmt = conn.createStatement();
				rs = stmt.executeQuery("select * from orders , qualitycontrol where orders.orderID=qualitycontrol.orderID AND qualitycontrol.qcDate >= '"+newDateString+"' AND qualitycontrol.qcDate <= '"+newDateStringg+"' AND orders.diopter > '"+startDiopter+"' AND orders.diopter < '"+endDiopter+"' ;");
				while(rs.next())
				{
					QCReportEntity myEntity = new QCReportEntity();
					myEntity.setOrderID(rs.getString(9).toString());
					myEntity.setType(rs.getString(2).toString());
					myEntity.setDiopter(rs.getString(3).toString());
					myEntity.setQuantity(rs.getString(5).toString());
					myEntity.setQcDate(rs.getString(8).toString());
					myEntity.setSerializationID(rs.getString(10).toString());
					myEntity.setFailSetFlag(rs.getString(11).toString());
					myEntity.setComments(rs.getString(13).toString());
					myEntity.setDeviationCountPlus05(rs.getString(14).toString());
					myEntity.setDeviationCountMinus05(rs.getString(15).toString());
					myEntity.setDeviationCountPlus10(rs.getString(16).toString());
					myEntity.setDeviationCountMinus10(rs.getString(17).toString());
					myEntity.setDeviationElse(rs.getString(18).toString());
					
					respond.add(myEntity);
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}
			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				//System.out.println("ERROR - server->mySQL-> initializeVPOfOperationsTabbedPolishReport()");
				respond.add(1,"unvalid");
				return respond;
			}
		}
		else
		{
			try
			{
				stmt = conn.createStatement();
				stmt1 = conn.createStatement();
				rs1 = stmt1.executeQuery("select productName from products where products.productFamily='"+family+"'");
				while (rs1.next())
				{
					rs = stmt.executeQuery("select * from orders , qualitycontrol where orders.orderID=qualitycontrol.orderID AND qualitycontrol.qcDate >= '"+newDateString+"' AND qualitycontrol.qcDate <= '"+newDateStringg+"' AND orders.diopter > '"+startDiopter+"' AND orders.diopter < '"+endDiopter+"' AND orders.type = '"+rs1.getString(1)+"';");
					while(rs.next())
					{
						QCReportEntity myEntity = new QCReportEntity();
						myEntity.setOrderID(rs.getString(9).toString());
						myEntity.setType(rs.getString(2).toString());
						myEntity.setDiopter(rs.getString(3).toString());
						myEntity.setQuantity(rs.getString(5).toString());
						myEntity.setQcDate(rs.getString(8).toString());
						myEntity.setSerializationID(rs.getString(10).toString());
						myEntity.setFailSetFlag(rs.getString(11).toString());
						myEntity.setComments(rs.getString(13).toString());
						myEntity.setDeviationCountPlus05(rs.getString(14).toString());
						myEntity.setDeviationCountMinus05(rs.getString(15).toString());
						myEntity.setDeviationCountPlus10(rs.getString(16).toString());
						myEntity.setDeviationCountMinus10(rs.getString(17).toString());
						myEntity.setDeviationElse(rs.getString(18).toString());
						
						respond.add(myEntity);
					}
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}

			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				respond.add(1,"unvalid");
				return respond;
			}

		}
	}


	/**
	 *This method is responsible on initializing the Packaging REPORT on the VPO dashboard  
	 * @param message1	0:initializeVPOfOperationsTabbedPackagingReport , 1:beginDate , 2:endDate , 3:startDiopter , 4:endDiopter , 5:family
	 * @return	ArrayList respond	0:initializeVPOfOperationsTabbedPackagingReport , 1:valid \ unvalid , 2,3,4...:VPOfOperationsTabbedPackagingReportEntity
	 */
	public ArrayList<Object> initializeVPOfOperationsTabbedPackagingReport(ArrayList<Object> message1) {
		Statement stmt=null,stmt1 = null;
		ResultSet rs,rs1;	
		int result=0;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
			
		String beginDate = message.get(1).toString();
		String endDate = message.get(2).toString();
	
		//System.out.print(beginDate.toString());
		String startDiopter = message.get(3).toString();
		String endDiopter = message.get(4).toString();
		String family = message.get(5).toString();
		
		// Translating formated date from :"dd/MM/yyyy" to :"yyyy/MM/dd".  
				final String OLD_FORMAT = "dd/MM/yyyy";
				final String NEW_FORMAT = "yyyy/MM/dd";
				String oldDateStringBegin = beginDate;
				String oldDateStringEnd = endDate;
				String newDateString=null;
				String newDateStringg=null;
				try{
					SimpleDateFormat sdf = new SimpleDateFormat(OLD_FORMAT);
					SimpleDateFormat sdff= new SimpleDateFormat(OLD_FORMAT);
					java.util.Date d = sdf.parse(oldDateStringBegin);
					java.util.Date dd = sdff.parse(oldDateStringEnd);
					sdf.applyPattern(NEW_FORMAT);
					sdff.applyPattern(NEW_FORMAT);
					newDateString = sdf.format(d);
					newDateStringg = sdff.format(dd);
					newDateString.toString();
					newDateStringg.toString();
				}catch(Exception e)
				{
					//e.printStackTrace();
				}
				// End translating formate of date .
		
		respond.add("initializeVPOfOperationsTabbedPackagingReport");
		if(family.equalsIgnoreCase("הכל"))
		{
			try
			{
				stmt = conn.createStatement();
				rs = stmt.executeQuery("SELECT packagereport.orderID,orders.quantity,orders.diopter,orders.type,packagereport.date,packagereport.quantity,employeepackaging.pName,packagereport.packageStep FROM hanitdb.packagereport,employeepackaging,orders where employeepackaging.pEmployeeID=packagereport.pEmployeeID AND orders.orderID=packagereport.orderID AND packagereport.date >= '"+newDateString+"' AND packagereport.date <= '"+newDateStringg+"' AND orders.diopter >= '"+startDiopter+"' AND orders.diopter <= '"+endDiopter+"' ; ;");
				while(rs.next())
				{
					VPOfOperationsTabbedPackagingReportEntity myEntity = new VPOfOperationsTabbedPackagingReportEntity();
					myEntity.setOrderID(rs.getString(1).toString());
					myEntity.setTotalQuantity(rs.getString(2).toString());
					myEntity.setDiopter(rs.getString(3).toString());
					myEntity.setType(rs.getString(4).toString());
					myEntity.setDate(rs.getString(5).toString());
					myEntity.setQuantityPacked(rs.getString(6).toString());
					myEntity.setEmployeeName(rs.getString(7).toString());
					myEntity.setSide(rs.getString(8).toString());
					
					respond.add(myEntity);
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}
			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				//System.out.println("ERROR - server->mySQL-> initializeVPOfOperationsTabbedPackagingReport()");
				respond.add(1,"unvalid");
				return respond;
			}
		}
		else
		{
			try
			{
				stmt = conn.createStatement();
				stmt1 = conn.createStatement();
				rs1 = stmt1.executeQuery("select productName from products where products.productFamily='"+family+"'");
				while (rs1.next())
				{
					rs = stmt.executeQuery("SELECT packagereport.orderID,orders.quantity,orders.diopter,orders.type,packagereport.date,packagereport.quantity,employeepackaging.pName,packagereport.packageStep FROM hanitdb.packagereport,employeepackaging,orders where employeepackaging.pEmployeeID=packagereport.pEmployeeID AND orders.orderID=packagereport.orderID AND packagereport.date >= '"+newDateString+"' AND packagereport.date <= '"+newDateStringg+"' AND orders.diopter >= '"+startDiopter+"' AND orders.diopter <= '"+endDiopter+"' AND orders.type = '"+rs1.getString(1).toString()+"' ;");
					while(rs.next())
					{
						VPOfOperationsTabbedPackagingReportEntity myEntity = new VPOfOperationsTabbedPackagingReportEntity();
						myEntity.setOrderID(rs.getString(1).toString());
						myEntity.setTotalQuantity(rs.getString(2).toString());
						myEntity.setDiopter(rs.getString(3).toString());
						myEntity.setType(rs.getString(4).toString());
						myEntity.setDate(rs.getString(5).toString());
						myEntity.setQuantityPacked(rs.getString(6).toString());
						myEntity.setEmployeeName(rs.getString(7).toString());
						myEntity.setSide(rs.getString(8).toString());
						
						respond.add(myEntity);
					}
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}

			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				respond.add(1,"unvalid");
				return respond;
			}
			
			
		}
	}

	/**
	 *This method is responsible on initializing the Hashmasha REPORT on the VPO dashboard  
	 * @param message1	0:initializeVPOfOperationsTabbedHashmashaReport , 1:beginDate , 2:endDate , 3:startDiopter , 4:endDiopter , 5:family
	 * @return	ArrayList respond	0:initializeVPOfOperationsTabbedHashmashaReport , 1:valid \ unvalid , 2,3,4...:VPOfOperationsTabbedHashmashaReportEntity
	 */

	public ArrayList<Object> initializeVPOfOperationsTabbedHashmashaReport(ArrayList<Object> message1) {
		Statement stmt = null,stmt1 = null;
		ResultSet rs,rs1;	
		int result=0;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
			
		String beginDate = message.get(1).toString();
		String endDate = message.get(2).toString();
	
		//System.out.print(beginDate.toString());
		String startDiopter = message.get(3).toString();
		String endDiopter = message.get(4).toString();
		String family = message.get(5).toString();
		
		// Translating formated date from :"dd/MM/yyyy" to :"yyyy/MM/dd".  
				final String OLD_FORMAT = "dd/MM/yyyy";
				final String NEW_FORMAT = "yyyy/MM/dd";
				String oldDateStringBegin = beginDate;
				String oldDateStringEnd = endDate;
				String newDateString=null;
				String newDateStringg=null;
				try{
					SimpleDateFormat sdf = new SimpleDateFormat(OLD_FORMAT);
					SimpleDateFormat sdff= new SimpleDateFormat(OLD_FORMAT);
					java.util.Date d = sdf.parse(oldDateStringBegin);
					java.util.Date dd = sdff.parse(oldDateStringEnd);
					sdf.applyPattern(NEW_FORMAT);
					sdff.applyPattern(NEW_FORMAT);
					newDateString = sdf.format(d);
					newDateStringg = sdff.format(dd);
					newDateString.toString();
					newDateStringg.toString();
				}catch(Exception e)
				{
					//e.printStackTrace();
				}
				// End translating formate of date .
				respond.add("initializeVPOfOperationsTabbedHashmashaReport");
		if(family.equalsIgnoreCase("הכל"))
		{
			try
			{
				stmt = conn.createStatement();
				rs =stmt.executeQuery("select packagehashmasha.hashmashaOrderID , packagehashmasha.originalOrderID ,packagehashmasha.comments ,orders.quantity from packagehashmasha , orders where orders.orderID = packagehashmasha.originalOrderID AND orders.orderdate >= '"+newDateString+"' AND orders.orderdate <= '"+newDateStringg+"' AND orders.diopter >= '"+startDiopter+"' AND orders.diopter <= '"+endDiopter+"';");
				while(rs.next())
				{
					VPOfOperationsTabbedHashmashaReportEntity myEntity = new VPOfOperationsTabbedHashmashaReportEntity();
					myEntity.setHashmashaID(rs.getString(1).toString());
					myEntity.setOriginalOrderId(rs.getString(2).toString());
					myEntity.setComments(rs.getString(3).toString());
					myEntity.setQuantity(rs.getString(4).toString());
					
					respond.add(myEntity);
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}
			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				//System.out.println("ERROR - server->mySQL-> initializeVPOfOperationsTabbedHashmashaReport()");
				respond.add(1,"unvalid");
				return respond;
			}
		}
		else{
			try
			{
				stmt = conn.createStatement();
				stmt1 = conn.createStatement();
				rs1 = stmt1.executeQuery("select productName from products where products.productFamily='"+family+"'");
				while (rs1.next())
				{
					rs = stmt.executeQuery("select packagehashmasha.hashmashaOrderID , packagehashmasha.originalOrderID ,packagehashmasha.comments ,orders.quantity from packagehashmasha , orders where orders.orderID = packagehashmasha.originalOrderID AND orders.orderdate >= '"+newDateString+"' AND orders.orderdate <= '"+newDateStringg+"' AND orders.diopter >= '"+startDiopter+"' AND orders.diopter <= '"+endDiopter+"' AND orders.type = '"+rs1.getString(1).toString()+"';");
					while(rs.next())
					{
						VPOfOperationsTabbedHashmashaReportEntity myEntity = new VPOfOperationsTabbedHashmashaReportEntity();
						myEntity.setHashmashaID(rs.getString(1).toString());
						myEntity.setOriginalOrderId(rs.getString(2).toString());
						myEntity.setComments(rs.getString(3).toString());
						myEntity.setQuantity(rs.getString(4).toString());
						////System.out.println("dd");
						respond.add(myEntity);
					}
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}

			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				respond.add(1,"unvalid");
				return respond;
			}
			
		}
	}
	/**
	 *This method is responsible on initializing the Discard REPORT on the VPO dashboard  
	 * @param message1	0:initializeVPOfOperationsTabbedDiscardReport , 1:beginDate , 2:endDate , 3:startDiopter , 4:endDiopter , 5:family
	 * @return	ArrayList respond	0:initializeVPOfOperationsTabbedDiscardReport , 1:valid \ unvalid , 2,3,4...:VPOfOperationsTabbedDiscardReportEntity
	 */
	
	public ArrayList<Object> initializeVPOfOperationsTabbedDiscardReport(ArrayList<Object> message1) {
		Statement stmt = null,stmt1=null;
		ResultSet rs,rs1;	
		int result=0;
		ArrayList<Object>message=message1 ;
		ArrayList<Object>respond = new ArrayList<>();
			
		String beginDate = message.get(1).toString();
		String endDate = message.get(2).toString();
	
		//System.out.print(beginDate.toString());
		String startDiopter = message.get(3).toString();
		String endDiopter = message.get(4).toString();
		String family = message.get(5).toString();
		
		// Translating formated date from :"dd/MM/yyyy" to :"yyyy/MM/dd".  
				final String OLD_FORMAT = "dd/MM/yyyy";
				final String NEW_FORMAT = "yyyy/MM/dd";
				String oldDateStringBegin = beginDate;
				String oldDateStringEnd = endDate;
				String newDateString=null;
				String newDateStringg=null;
				try{
					SimpleDateFormat sdf = new SimpleDateFormat(OLD_FORMAT);
					SimpleDateFormat sdff= new SimpleDateFormat(OLD_FORMAT);
					java.util.Date d = sdf.parse(oldDateStringBegin);
					java.util.Date dd = sdff.parse(oldDateStringEnd);
					sdf.applyPattern(NEW_FORMAT);
					sdff.applyPattern(NEW_FORMAT);
					newDateString = sdf.format(d);
					newDateStringg = sdff.format(dd);
					newDateString.toString();
					newDateStringg.toString();
				}catch(Exception e)
				{
					//e.printStackTrace();
				}
				// End translating formate of date .
		
		respond.add("initializeVPOfOperationsTabbedDiscardReport");
		if(family.equalsIgnoreCase("הכל"))
		{
			try
			{
				stmt = conn.createStatement();
				rs = stmt.executeQuery("SELECT orders.orderID  , orders.quantity , orders.diopter , orders.type , packagediscard.quantity, packagediscard.discardComments FROM hanitdb.packagediscard , orders where orders.orderID = packagediscard.orderID AND packagediscard.discardDate >= '"+newDateString+"' AND packagediscard.discardDate <= '"+newDateStringg+"' AND orders.diopter >= '"+startDiopter+"' AND orders.diopter <= '"+endDiopter+"' ;");
				while(rs.next())
				{
					VPOfOperationsTabbedDiscardReportEntity myEntity = new VPOfOperationsTabbedDiscardReportEntity();
					myEntity.setOrderID(rs.getString(1).toString());
					myEntity.setTotalQuantity(rs.getString(2).toString());
					myEntity.setDiopter(rs.getString(3).toString());
					myEntity.setType(rs.getString(4).toString());
					myEntity.setDiscardQuantity(rs.getString(5).toString());
					myEntity.setComments(rs.getString(6).toString());
					
					respond.add(myEntity);
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}
			}
			catch(SQLException e)
			{
				//e.printStackTrace();
			
				respond.add(1,"unvalid");
				return respond;
			}
		}
		else{
			try
			{
				stmt = conn.createStatement();
				stmt1 = conn.createStatement();
				rs1 = stmt1.executeQuery("select productName from products where products.productFamily='"+family+"'");
				while (rs1.next())
				{
					rs = stmt.executeQuery("SELECT orders.orderID  , orders.quantity , orders.diopter , orders.type , packagediscard.quantity, packagediscard.discardComments FROM hanitdb.packagediscard , orders where orders.orderID = packagediscard.orderID AND packagediscard.discardDate >= '"+newDateString+"' AND packagediscard.discardDate <= '"+newDateStringg+"' AND orders.diopter >= '"+startDiopter+"' AND orders.diopter <= '"+endDiopter+"' AND orders.type='"+rs1.getString(1).toString()+"' ;");
					while(rs.next())
					{
						VPOfOperationsTabbedDiscardReportEntity myEntity = new VPOfOperationsTabbedDiscardReportEntity();
						myEntity.setOrderID(rs.getString(1).toString());
						myEntity.setTotalQuantity(rs.getString(2).toString());
						myEntity.setDiopter(rs.getString(3).toString());
						myEntity.setType(rs.getString(4).toString());
						myEntity.setDiscardQuantity(rs.getString(5).toString());
						myEntity.setComments(rs.getString(6).toString());
						
						respond.add(myEntity);
					}
				}
				if(respond.size()==1)
				{
					respond.add(1,"unvalid");
					return respond;
				}
				else
				{
					respond.add(1,"valid");
					return respond;
				}

			}
			catch(SQLException e)
			{
				//e.printStackTrace();
				respond.add(1,"unvalid");
				return respond;
			}
		}
	}


	/**
	 * 
	 * @param requestParameters report parameters
	 * @return report data 
	 */
	public ArrayList<Object> getRawMaterialsReport(RequestParameters requestParameters)
	{
		ArrayList<Object> response = new ArrayList<Object>();
		int counter = 0;
		response.add("PlaceHolder");
		response.add(counter);
		
		Statement stmt;
		ResultSet rs = null;

			try
			{
				stmt= conn.createStatement();
				if (requestParameters.getFamily().equalsIgnoreCase("הכל"))
				{	
					
					rs = stmt.executeQuery("SELECT materials.materialID, SUM(qualitycontrol.deviationCountMinus10), SUM(qualitycontrol.deviationCountMinus05), SUM(qualitycontrol.deviationCountPlus05), SUM(qualitycontrol.deviationCountPlus10), SUM(qualitycontrol.deviationCountElse), SUM(orders.quantity) FROM materials, orders, qualitycontrol WHERE materials.orderID = orders.orderID AND qualitycontrol.orderID = materials.orderID AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND orders.orderdate >= '" + requestParameters.getStartTimestamp() + "' AND orders.orderdate <= '" + requestParameters.getEndTimestamp() + "' GROUP BY materials.materialID;");
				}
				else
				{
					
					rs = stmt.executeQuery("SELECT materials.materialID, SUM(qualitycontrol.deviationCountMinus10), SUM(qualitycontrol.deviationCountMinus05), SUM(qualitycontrol.deviationCountPlus05), SUM(qualitycontrol.deviationCountPlus10), SUM(qualitycontrol.deviationCountElse), SUM(orders.quantity) FROM materials, orders, qualitycontrol, products WHERE products.productName = orders.type AND orders.orderID = materials.orderID AND qualitycontrol.orderID = materials.orderID AND orders.diopter >= '" + requestParameters.getStartDiopter() + "' AND orders.diopter <= '" + requestParameters.getEndDiopter() + "' AND orders.orderdate >= '" + requestParameters.getStartTimestamp() + "' AND orders.orderdate <= '" + requestParameters.getEndTimestamp() + "' AND orders.type = products.productName AND products.productFamily = '" + requestParameters.getFamily() + "' GROUP BY materials.materialID;");
				}
				
				while (rs.next())
				{
					QCReport report = new QCReport();
		
				
					report.setRawMaterialID(rs.getString(1));
					report.setDeviationCountMinus10(rs.getInt(2));
					report.setDeviationCountMinus05(rs.getInt(3));
					report.setDeviationCountPlus05(rs.getInt(4));
					report.setDeviationCountPlus10(rs.getInt(5));
					report.setDeviationCountElse(rs.getInt(6));
					report.setFailSetFlag(rs.getInt(7));
											
					response.add(report);
					counter++;					
				}
				
				response.set(1,counter);
				
				if (counter == 0)
					response.set(0,"empty");
				else
					response.set(0,"valid");
				
				stmt.close();
				rs.close();
			} 
			catch (SQLException e)
			{
				response.set(0, "SQL Error");
				
				//e.printStackTrace();
			}
		

		return response;
	}

/**
 * 
 * @return exports database into excel worksheets as used before the switch to the Hanit system
 */
	public ArrayList<Object> exportToExcel()
	{
		
		ArrayList<Object> response = new ArrayList<Object>();
	
		response.add("Valid");
		
		
		// create MPM reports
		Statement stmt1;
		try
		{
			stmt1 = conn.createStatement();
			stmt1.executeQuery("SELECT 'Order','type','diopter','orderdate','quantity' UNION SELECT orderID,type,diopter,orderdate,quantity FROM  orders INTO OUTFILE 'c:/Hanit/tapi.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '" + "\"" + "' LINES TERMINATED BY '" + "\\" + "n'"); 
			
			stmt1.close();
		} 
		catch (SQLException e)
		{
			
		//	e.printStackTrace();
			response.set(0, "Error");
		}
		
		
		
		// create engrave reports
		try 
		{
		
			Statement stmt2 = conn.createStatement();
			stmt2.executeQuery("SELECT 'Date','Shift','Name','Order','Line','Type','Diopter','SideA','SideB','Comments1','Comments2','FreeText' UNION SELECT engrave.engraveDate, engrave.shift, employees.name, engrave.orderID, engrave.line, orders.type, orders.diopter, engrave.sideAquantity, engrave.sideBquantity, engrave.comments1, engrave.comments2, engrave.freeText FROM orders,employees,engrave WHERE engrave.orderID = orders.orderID AND employees.employeeID = engrave.employeeID INTO OUTFILE 'c:/Hanit/engrave.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '" + "\"" + "' LINES TERMINATED BY '" + "\\" + "n'");
			stmt2.close();
		} 
		catch (SQLException e) 
		{
			
			//e.printStackTrace();
			response.set(0, "Error");
		}
		
		
		// create polish reports
		try 
		{
			Statement stmt3 = conn.createStatement();
			stmt3.executeQuery("SELECT 'Jar','Pellet_Cycles','Topline','Order','Comment','Begin_Date','Original_End_Date','End_Date' UNION SELECT jars.jarID, jars.pelletesCycles, polish.topline, ordersinpolish.orderID, polish.comment, polish.beginDate, polish.originalEndDate, polish.endDate FROM  jars, ordersinpolish, polish WHERE ordersinpolish.polishID = polish.polishID AND polish.jarID = jars.jarID INTO OUTFILE 'c:/Hanit/polish.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '" + "\"" + "' LINES TERMINATED BY '" + "\\" + "n'");
			stmt3.close();
			
			Statement stmt31 = conn.createStatement();
			stmt31.executeQuery("SELECT 'originalOrderID','repolishOrderID','pudra','engraveMarks','scratches','stuckChip' UNION SELECT originalOrderID,repolishOrderID,pudra,engraveMarks,scratches,stuckChip FROM repolishsets INTO OUTFILE 'c:/Hanit/repolish.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '" + "\"" + "' LINES TERMINATED BY '" + "\\" + "n'");
			stmt31.close();
		} 
		catch (SQLException e)
		{
			
			//e.printStackTrace();
			response.set(0, "Error");
		}
		
		// create QA reports
		try
		{
			Statement stmt4 = conn.createStatement();
			stmt4.executeQuery("SELECT 'Date','Type','Diopter','Order','Engrave_Date','Line','Sterization_Batch','Fail','Repolish','scratches','stuckChip','engraveMarks','aoptic','diopterDeviation','surface','dirt','dropedOrLost','bumps','hydration','fractures','milling','asymmetry','characteristics','orange','c1TurnLens','c2ChippedEdge','c3CuttingChips','c4Scratches','c5Prism','c6FoldedLens','c7OODDiameter','c8OODThickness','c9Lost','c10LatheMarks','c11ChippedHole','c12NoHole','c13UndefinedParticles','c14FoggyResolution','c15OODDiopterPower','c16OODPlanoPower','engravingStationEngraving','engravingStationCharacteristics','engravingStationDroppedOrLost','engravePlan','dryFails','Comments' UNION SELECT qualitycontrol.qcDate, orders.type, orders.diopter,qualitycontrol.orderID,engrave.engraveDate,engrave.line,qualitycontrol.sterilizationID,qualitycontrol.failsetFlag,qualitycontrol.repolishSetFlag, failsets.scratches, failsets.stuckChip, failsets.engraveMarks, failsets.aoptic, failsets.diopterDeviation, failsets.surface, failsets.dirt, failsets.dropedOrLost, failsets.bumps, failsets.hydration, failsets.fractures, failsets.milling, failsets.asymmetry, failsets.characteristics, failsets.orange, failsets.c1TurnLens, failsets.c2ChippedEdge, failsets.c3CuttingChips, failsets.c4Scratches, failsets.c5Prism, failsets.c6FoldedLens, failsets.c7OODDiameter, failsets.c8OODThickness, failsets.c9Lost, failsets.c10LatheMarks, failsets.c11ChippedHole, failsets.c12NoHole, failsets.c13UndefinedParticles, failsets.c14FoggyResolution, failsets.c15OODDiopterPower, failsets.c16OODPlanoPower, failsets.engravingStationEngraving, failsets.engravingStationCharacteristics, failsets.engravingStationDroppedOrLost, failsets.engravePlan, failsets.dryFails, qualitycontrol.comments FROM orders,engrave,qualitycontrol,failsets WHERE engrave.orderID = qualitycontrol.orderID AND qualitycontrol.orderID = orders.orderID AND qualitycontrol.orderID = failsets.orderID INTO OUTFILE 'c:/Hanit/qualitycontrol.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '" + "\"" + "' LINES TERMINATED BY '" + "\\" + "n'");
			stmt4.close();
		} 
		catch (SQLException e)
		{
			
			//e.printStackTrace();
			response.set(0, "Error");
		}
		
		// create packaging reports
		try
		{
			Statement stmt5 = conn.createStatement();
			stmt5.executeQuery("SELECT 'Order','Date','Quantity','Employee','Packaging','Discard_Quantity','Discard_Date','Discard_Comment' UNION SELECT packagereport.orderID,packagereport.date, packagereport.quantity,employeepackaging.pName, packagereport.packageStep, SUM(packagediscard.quantity),packagediscard.discardDate ,packagediscard.discardComments FROM packagereport, employeepackaging, packagediscard WHERE packagereport.orderID = packagediscard.orderID AND packagereport.pEmployeeID = employeepackaging. pEmployeeID INTO OUTFILE 'c:/Hanit/package.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '" + "\"" + "' LINES TERMINATED BY '" + "\\" + "n'");
			stmt5.close();
		}
		catch (SQLException e) 
		{
			
			//e.printStackTrace();
			response.set(0, "Error");
		}
		
		return response;
	}
	
	
	/**
	 * Creates a large datadump of the entire database into a big excel sheet.
	 * @param from begin date
	 * @param to end date
	 * @param path destination folder
	 * @return validity string
	 */
	public ArrayList<Object> largeTableToExcel(String from, String to, String path)
	{
		
		ArrayList<Object> response = new ArrayList<Object>();
		String today = Services.getTodayDifferentFormat();
		response.add("valid");
		
	
		Statement stmt1;
		try
		{
			stmt1 = conn.createStatement();
			String query = "SELECT 'Order', 'Type', 'Diopter','Order_Date', 'Quantity', 'Jar','Polish_Begin_Date', 'Polish_Original_End_Date', 'Polish_Actual_End_Date', 'Topline',	'Quality_Control_Date', 'Sterilization_Batch', 'Fails',	'Repolish', 'Diopter_Deviation_Plus05',	'Diopter_Deviation_Minus05', 'Diopter_Deviation_Plus10','Diopter_Deviation_Minus10', 'Diopter_Deviation_Other',	'Fail_Scratches', 'Fail_StuckChip', 'Fail_Engrave_Marks', 'Fail_AOptic','Fail_Surface', 'Fail_Dirt', 'Fail_Droped_Or_Lost', 'Fail_Bumps','Fail_Hydration', 'Fail_Loops', 'Fail_Fractures', 'Fail_Milling',	'Fail_Asymmetry', 'Fail_Characteristics', 'Fail_Orange',	'Fail_c1TurnLens', 'Fail_c2ChippedEdge', 'Fail_c3CuttingChips', 'Fail_c4Scratches',	'Fail_c5Prism', 'Fail_c6FoldedLens', 'Fail_c7OODDiameter',	'Fail_c8OODThickness', 'Fail_c9Lost', 'Fail_c10LatheMarks', 'Fail_c11ChippedHole',	'Fail_c12NoHole', 'Fail_c13UndefinedParticles', 'Fail_c14FoggyResolution',	'Fail_c15OODDiopterPower', 'Fail_c16OODPlanoPower', 'Fail_Engraving_Station_Engrave_Marks',	'Fail_Engraving_Station_Characteristics', 'Fail_Engraving_Station_Dropped_Or_Lost',	'Fail_Engrave_Plan', 'Fail_Fails_In_Dry', 'Repolish_Order',	'Repolish_Pudra', 'Repolish_Engrave_Marks', 'Repolish_Scratches','Repolish_Stuck_Chip', 'Packaging_Date','Packaged_Quantity', 'Packaging_Employees',	'Packaging_Discarded', 'Discard_Date' UNION SELECT orders.orderID, orders.type, orders.diopter,orders.orderdate, orders.quantity, COALESCE(polish.jarID, 0),COALESCE(ordersinpolish.beginDate,0), COALESCE(polish.originalEndDate,0), COALESCE(polish.endDate,0), COALESCE(polish.topline,0),	COALESCE(qualitycontrol.qcDate,0), COALESCE(qualitycontrol.sterilizationID,0), COALESCE(qualitycontrol.failSetFlag,0) as Fails,	COALESCE(qualitycontrol.repolishSetFlag,0) as Repolish, COALESCE(qualitycontrol.deviationCountPlus05,0),	COALESCE(qualitycontrol.deviationCountMinus05,0), COALESCE(qualitycontrol.deviationCountPlus10,0),	COALESCE(qualitycontrol.deviationCountMinus10,0), COALESCE(qualitycontrol.deviationCountElse,0),	COALESCE(failsets.scratches,0), COALESCE(failsets.stuckChip,0), COALESCE(failsets.engraveMarks,0), COALESCE(failsets.aoptic,0),	COALESCE(failsets.surface,0), COALESCE(failsets.dirt,0), COALESCE(failsets.dropedOrLost,0), COALESCE(failsets.bumps,0),	COALESCE(failsets.hydration,0), COALESCE(failsets.loops,0), COALESCE(failsets.fractures,0), COALESCE(failsets.milling,0),	COALESCE(failsets.asymmetry,0), COALESCE(failsets.characteristics,0), COALESCE(failsets.orange,0),	COALESCE(failsets.c1TurnLens,0), COALESCE(failsets.c2ChippedEdge,0), COALESCE(failsets.c3CuttingChips,0), COALESCE(failsets.c4Scratches,0),	COALESCE(failsets.c5Prism,0), COALESCE(failsets.c6FoldedLens,0), COALESCE(failsets.c7OODDiameter,0),	COALESCE(failsets.c8OODThickness,0), COALESCE(failsets.c9Lost,0), COALESCE(failsets.c10LatheMarks,0), COALESCE(failsets.c11ChippedHole,0),	COALESCE(failsets.c12NoHole,0), COALESCE(failsets.c13UndefinedParticles,0), COALESCE(failsets.c14FoggyResolution,0),COALESCE(failsets.c15OODDiopterPower,0), COALESCE(failsets.c16OODPlanoPower,0), COALESCE(failsets.engravingStationEngraving,0),	COALESCE(failsets.engravingStationCharacteristics,0), COALESCE(failsets.engravingStationDroppedOrLost,0),	COALESCE(failsets.engravePlan,0), COALESCE(failsets.dryFails,0), COALESCE(repolishsets.repolishOrderID,0),	COALESCE(repolishsets.pudra,0), COALESCE(repolishsets.engraveMarks,0), COALESCE(repolishsets.scratches,0),	COALESCE(repolishsets.stuckChip,0), COALESCE(GROUP_CONCAT(DISTINCT packagereport.date SEPARATOR ', '),0) as Packaging_Date,	COALESCE(SUM(packagereport.quantity),0) as Packaged_Quantity, COALESCE(GROUP_CONCAT(DISTINCT employeepackaging.pName SEPARATOR ', '),0) as Packaging_Employees,	COALESCE(SUM(packagediscard.quantity),0), COALESCE(GROUP_CONCAT(DISTINCT packagediscard.discardDate SEPARATOR ', '),0) INTO OUTFILE '" + path + today + ".csv' FIELDS TERMINATED BY ',' ENCLOSED BY '" + "\"" + "' LINES TERMINATED BY '" + "\\" + "n'" + " FROM orders LEFT OUTER JOIN	(SELECT orderID, engraveDate,line, GROUP_CONCAT(DISTINCT employees.name SEPARATOR ', ') as Engraving_Employees, SUM(sideAquantity), SUM(sideBquantity) "+
					"FROM engrave, employees WHERE engrave.employeeID = employees.employeeID GROUP BY engrave.orderID) engrave ON (orders.orderID = engrave.orderID ) LEFT OUTER JOIN ordersinpolish ON (orders.orderID = ordersinpolish.orderID) LEFT OUTER JOIN polish ON (ordersinpolish.polishID = polish.polishID) LEFT OUTER JOIN qualitycontrol ON (orders.orderID = qualitycontrol.orderID) LEFT OUTER JOIN failsets ON (orders.orderID = failsets.orderID) LEFT OUTER JOIN repolishsets ON (orders.orderID = repolishsets.originalOrderID) LEFT OUTER JOIN packagereport ON (orders.orderID = packagereport.orderID) LEFT OUTER JOIN packagediscard ON (orders.orderID = packagediscard.orderID) LEFT OUTER JOIN packagehashmasha ON (orders.orderID = packagehashmasha.originalOrderID) LEFT OUTER JOIN employeepackaging ON (packagereport.pEmployeeID = employeepackaging.pEmployeeID) WHERE orders.orderdate >= '" + from + "' AND orders.orderdate <= '" + to + "' GROUP BY orders.orderID ASC;";
	
			stmt1.executeQuery(query); 
			
			stmt1.close();
		} 
		catch (SQLException e)
		{
			
		//	e.printStackTrace();
			response.set(0, "Error");
		}
		return response;
	}
	
	
	
	
	
	
	
}
